To save the output of command prompt in a txt or csv output file using VBA Excel.
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.
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.