Make VBA wait until batch file is finished

Question

I am using VBA in my Access DB to launch a batch file which is linked to PS1 script.

That all works as intended. The issue is that I want to run some queries after that action is completed, but as it stands I need to babysit the whole thing. So I am looking for a solution to keep the VBA paused while the batch is running.

I found this article: https://danwagner.co/how-to-run-a-batch-file-and-wait-until-it-finishes-with-vba/

But the solution doesn't work for me for some reason. The batch runs, but the VBA just steams on ahead without pausing.

Here is my code:

Private Sub Button_UpdateOffline_Click()

Dim strCommand As String
Dim lngErrorCode As Long
Dim wsh As WshShell
Set wsh = New WshShell

DoCmd.OpenForm "Please_Wait"

'Run the batch file using the WshShell object
strCommand = Chr(34) & _
             "C:\Users\Rip\Q_Update.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

DoCmd.Close acForm, "Please_Wait"

End Sub

Here is my batch code if that helps:

START PowerShell.exe -ExecutionPolicy Bypass -Command "& 'C:\Users\Rip\PS1\OfflineFAQ_Update.ps1' "

Answer 1

Your batch code launches PowerShell, then closes.

VBA waits until your batch code has launched PowerShell, then continues. It has no way to know you actually want to wait until PowerShell has finished, since if you want to wait on that, you'd have to make your batch script wait as well.

So, either change the batch code to include /WAIT, in addition to the changes suggested in the comments:

START /wait PowerShell.exe -ExecutionPolicy Bypass -Command "& 'C:\Users\Rip\PS1\OfflineFAQ_Update.ps1' "

Or, open PowerShell directly without the batch file in between:

strCommand = "PowerShell.exe -ExecutionPolicy Bypass -Command ""& 'C:\Users\Rip\PS1\OfflineFAQ_Update.ps1' """