vba wait until batch file is complete

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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.
VBA Code:
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.
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,997
Replace the Shell line with:
VBA Code:
    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
 
Solution

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
Replace the Shell line with:
VBA Code:
    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

Board Regular
Joined
Feb 9, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
it just stalls like nothing is happening then I have to force close
 

Jake975

Board Regular
Joined
Feb 9, 2020
Messages
114
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,074
Messages
5,704,147
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top