vba wait until batch file is complete

[Jake975]

I have a program that will be running 3 versions of a batch file. The parameters are passed to the batch file each time. My issue right now is that vba opens each version at once. I would like it to wait until it has completed before running the next. here is the code i currently use

Public Sub mPDF()
Dim Loc As String
Dim out As String
Dim Source As String

Loc = Sheets("Home").Range("H1") + "\Temp.csv"
out = "G:\Shared drives\Test\Compiled" + "\Night Audit " & Format(Date - 1, "mm-dd-yyyy") & ".pdf"
Source = "G:\Shared drives\Test\sejda\Excel_PRS.bat"

Call Shell(Source & " " & """" & Loc & """" & " " & """" & out, vbNormalFocus)

End Sub

I found this somewhere and was wondering how to mash the two together to get vba to wait until the batch file is complete.

Option Explicit
Public Sub RunBatchFile()

    Dim strCommand As String
    Dim lngErrorCode As Long
    Dim wsh As WshShell
    Set wsh = New WshShell
   
    'Run the batch file using the WshShell object
    strCommand = Chr(34) & _
                 "C:\wshshell-fun\Create New 2015 Project Folder.bat" & _
                 Chr(34)
    lngErrorCode = wsh.Run(strCommand, _
                           WindowStyle:=0, _
                           WaitOnReturn:=True)
    If lngErrorCode <> 0 Then
        MsgBox "Uh oh! Something went wrong with the batch file!"
        Exit Sub
    End If
End Sub

Its not required I just want to remove some confusion. Thanks for your help.

 

[John_w]

Replace the Shell line with:

    Const Q As String = """"
    Dim wsh As Object
    Set wsh = CreateObject("Wscript.Shell")
    wsh.Run Q & Source & Q & " " & Q & Loc & Q & " " & Q & out & Q, 0, True

 

[Jake975]

Replace the Shell line with:

    Const Q As String = """"
    Dim wsh As Object
    Set wsh = CreateObject("Wscript.Shell")
    wsh.Run Q & Source & Q & " " & Q & Loc & Q & " " & Q & out & Q, 0, True

I will give it a shot and then let you know thank you for your help

 

[Jake975]

it just stalls like nothing is happening then I have to force close

 

[Jake975]

I got it to work the problem was you needed to interact with the command window and I didn't realize it the hidden making the application freeze in a way. I changed the 0 to a 1 and it loads each version of the batch file one at a time as desired. Thank you for your help