Excel VBA- FSO Execute batch file

By Ajeet Kumar

EXCEL VBA TO EXECUTE A BATCH FILE

The steps used in the below code are as follows:

1. Use FileDialog to select a folder where batch file will be generated and executed.
2. Create an instance of FileSystemObject.
3. Create an instance of Text File Stream
4. Write DOS commands inside the file and save it.
5. Use Shell command along with Environ Function to Execute the batch file.

CODE

Sub pExecuteBatchFile()

    Dim FSO As Object
    Dim objTextStream As Object
    Dim strPath As String
    Dim ReturnV As Double
    
    On Error GoTo labelERR
    
    With Application.FileDialog(msoFileDialogFolderPicker)

        .AllowMultiSelect = False
        .Title = "Select A Folder"
        .Show
        strPath = .SelectedItems(1)

    End With
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objTextStream = FSO.CreateTextFile(strPath & "\ABC.bat")
    
    With objTextStream

        .WriteLine "CD/"
        .WriteLine "CD """ & strPath & """"
        .WriteLine "DIR/b>LIST.txt"
        .Close

    End With
    
    '''COMSPEC is an environment variable that lists the path to the command shell executable.

    Call Shell(Environ$("COMSPEC") & " /c  " & strPath & "\ABC.bat", vbHide)

labelERR:

    If Err.Number <> 0 Then
        MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description
    End If

End Sub