Save the output of command prompt in a text or csv file using VBA Excel

Save command prompt output vba

Objective

To save the output of command prompt in a txt or csv output file using VBA Excel.

Approach

In the code below we have executed the dir command for a folder and saved the output in a text file.

Here, first we have invoked the command prompt in VBA Excel by calling shell function within the subroutine. Then we have executed the Dir command in the command prompt, we have redirected the output of DOS command by using “>” sign. Here we have sent the output in a txt file, but we can also replace txt with csv to change the format of output file.

In the code below we have tried to find the list of folders and files in a particular folder. We have used recursive search here.Here first we check if the source folder exists or not, if the folder does not exist then the code will move to “else” condition and code will be stopped here. If the folder exists, then we shall invoke the command prompt and execute the Dir command. Here we shall save the output of Dir command to Directory_Details.txt file. This file will be stored in the source folder, i.e. the folder for which we are running the Dir command.

Code

Sub SaveDosCommandOutput()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim MyFolderPath As String


'Path where the folder is located, change the path as per your requirement
MyFolderPath = "C:\Users\myusername\Desktop\R"

'Change the current directory to required folder
ChDir MyFolderPath

'Check if the folder already exists or not
If Dir(MyFolderPath, vbDirectory) <> "" Then

'List all folders and subfolders using dos command in file directory_details.csv
Call Shell("cmd.exe  /C" & "dir  /s /b /o:n  > Directory_Details.txt " & "  /Q")


'Display a message box if folder does not exist
Else
MsgBox "Folder does not Exist"
End If
End Sub

 

In the image below we can see that output of the Dir command is saved in the text file.

 

Save command prompt output vba

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/shell-function