VBA - Syntax to have VBA wait until the .exe runs before processing the rest of the code

[parkerbelt]

I have the following code that I run from an excel spreadsheet and it runs a .exe file, which opens up another excel spreadsheet and does some things. I want the program to run the .exe file and when it is finished, the spreadsheet that it opens will have been activated - at that point, I want the remaining code to run.

The problem that I'm having is that VBA runs the .exe file, but processes the remaining code right afterwards, on the first spreadsheet, and doesn't wait for the .exe to finish. I've been searching the internet and found that people are using ShellAndWait and I've been trying to make that work, but I can't seem to get my syntax correct.

Anyone have any suggestions?
Here's my code:

Sub Angus()                Dim AngusAppPath As String, varProc As Variant
    On Error Resume Next
    AngusAppPath = "S:\James Jech Stuff\Program 6 - add vba - Copy.exe" 
    varProc = Shell(AngusAppPath, 1)





    Range("A18:P33").Select    Selection.Copy    Range("A35").Select    ActiveSheet.Paste        Rows("37:39").Select    Application.CutCopyMode = False    Selection.Delete Shift:=xlUp    Rows("38:40").Select    Selection.Delete Shift:=xlUp    Rows("39:41").Select    Selection.Delete Shift:=xlUp    Rows("39:41").Select    Range("G39").Activate    Selection.Delete Shift:=xlUp    Range("A37").Select    ActiveCell.FormulaR1C1 = "=R[-31]C+R[-14]C/2"    Range("A37").Select    Selection.AutoFill Destination:=Range("A37:P37"), Type:=xlFillDefault    Range("A37:P37").Select        Range("A39").Select    ActiveCell.FormulaR1C1 = "=R[-25]C+R[-8]C/2"    Range("A39").Select    Selection.AutoFill Destination:=Range("A39:L39"), Type:=xlFillDefault    Range("A39:L39").Select        Range("N31").SelectEnd Sub

 

[Kenneth Hobson]

Get Chip Pearson's ShellAndWait(). http://www.cpearson.com/excel/ShellAndWait.aspx

 

[parkerbelt]

Get Chip Pearson's ShellAndWait(). http://www.cpearson.com/excel/ShellAndWait.aspx

Thanks Kenneth! I've seen that and have been studying it. I'm new to coding and I'm self taught, so I'm having a hard time understanding how I am to use that with the code that I've provided.

Where do I insert my "S:\James Jech Stuff\Program 6 - add vba - Copy.exe" ?
Do I need to put anything else into the code or do I just put the "S:\James Jech Stuff\Program 6 - add vba - Copy.exe" in there somewhere?
I'm assuming that I can just copy all of the "Complete Code Module" into VBA, below my Sub or something like that.
Like I say, I'm new to this. I'm sure it is something easy, but I just don't get it. Thanks!

 

[parkerbelt]

Here's the code that I'm using again, the last paste I did doesn't look very good:

Sub Angus()



Dim AngusAppPath As String, varProc As Variant

On Error Resume Next
AngusAppPath = "S:\James Jech Stuff\Program 6 - add vba - Copy.exe" 'Your path here

varProc = Shell(AngusAppPath, 1)


Range("A18:P33").Select
Selection.Copy
Range("A35").Select
ActiveSheet.Paste

Rows("37:39").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Rows("38:40").Select
Selection.Delete Shift:=xlUp
Rows("39:41").Select
Selection.Delete Shift:=xlUp
Rows("39:41").Select
Range("G39").Activate
Selection.Delete Shift:=xlUp
Range("A37").Select
ActiveCell.FormulaR1C1 = "=R[-31]C+R[-14]C/2"
Range("A37").Select
Selection.AutoFill Destination:=Range("A37:P37"), Type:=xlFillDefault
Range("A37:P37").Select

Range("A39").Select
ActiveCell.FormulaR1C1 = "=R[-25]C+R[-8]C/2"
Range("A39").Select
Selection.AutoFill Destination:=Range("A39:L39"), Type:=xlFillDefault
Range("A39:L39").Select

Range("N31").Select
End Sub

 

[John_w]

You can do it in 1 line with the Windows Script Host Run method:

    CreateObject("Wscript.Shell").Run AngusAppPath, 1, True     '0=hide window, 1=show window; True = wait on return

 

[parkerbelt]

I tried your code, but the .exe never ran -it skipped right over it. Thanks though!

You can do it in 1 line with the Windows Script Host Run method:

    CreateObject("Wscript.Shell").Run AngusAppPath, 1, True     '0=hide window, 1=show window; True = wait on return

Here's how I used it:
Sub Angus()



Dim AngusAppPath As String, varProc As Variant

On Error Resume Next
AngusAppPath = "S:\James Jech Stuff\Program 6 - add vba - Copy.exe" 'Your path here

CreateObject("Wscript.Shell").Run AngusAppPath, 1, True '0=hide window, 1=show window; True = wait on return

'varProc = Shell(AngusAppPath, 1)


Range("A18:P33").Select
Selection.Copy
Range("A35").Select
ActiveSheet.Paste

Rows("37:39").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Rows("38:40").Select
Selection.Delete Shift:=xlUp
Rows("39:41").Select
Selection.Delete Shift:=xlUp
Rows("39:41").Select
Range("G39").Activate
Selection.Delete Shift:=xlUp
Range("A37").Select
ActiveCell.FormulaR1C1 = "=R[-31]C+R[-14]C/2"
Range("A37").Select
Selection.AutoFill Destination:=Range("A37:P37"), Type:=xlFillDefault
Range("A37:P37").Select

Range("A39").Select
ActiveCell.FormulaR1C1 = "=R[-25]C+R[-8]C/2"
Range("A39").Select
Selection.AutoFill Destination:=Range("A39:L39"), Type:=xlFillDefault
Range("A39:L39").Select

Range("N31").Select
End Sub

 

[Kenneth Hobson]

Did the Wscript routine run your EXE? Some shell routines will require strings to be surrounded by double quotes. e.g.

Sub Main()
  Dim AngusAppPath As String
  AngusAppPath = "S:\James Jech Stuff\Program 6 - add vba - Copy.exe"  'Your path here"

  If Dir(AngusAppPath) = "" Then
    MsgBox "File does not exist." & vbLf & AngusAppPath, vbCritical, "Macro Ending"
    Exit Sub
  End If
  
  '0=hide window, 1=show window; True = wait on return
  
  CreateObject("Wscript.Shell").Run """" & AngusAppPath & """", 1, True
End Sub

Not as robust as Chip's, Ron de Bruin has a similar API method. Copy his code from this link to the end of the first public sub. API code is required to be at the top of a Module.
https://www.rondebruin.nl/win/winfiles/7zip_unzipexamples.txt

Chip documents his routines well. Intellisense alone makes using them pretty self evident just like others like Shell(). About midway in Chip's link, you will see this link to his BAS file. The unzipped BAS file imports as a Module. You can Import via VBE's Insert menu or right clicking in VBE's Project Explorer. http://www.cpearson.com/Zips/modShellAndWait.zip

 

[John_w]

I tried your code, but the .exe never ran -it skipped right over it. Thanks though!

As Kenneth said, try surrounding the exe file name and path with double quotes, like this:

AngusAppPath = Chr(34) & "S:\James Jech Stuff\Program 6 - add vba - Copy.exe" & Chr(34) 'Your path here

CreateObject("Wscript.Shell").Run AngusAppPath, 1, True '0=hide window, 1=show window; True = wait on return

Also delete the On Error Resume Next as that hides any run-time errors. You want to know about any errors so you can program accordingly.

Please use CODE tags - click the # icon in the message editor.

 

[parkerbelt]

That worked! Thank you!! And, I'm glad that I don't have a huge block of code just for something small like that.

On another note, now when the .exe runs from VBA, the Ctrl+c to Copy and the Ctrl+v to paste that are in the exe aren't working. Is there something I need to put in the code to make those available?[
The .exe is a AutoIT program. Here's the code:

Run('C:\Program Files (x86)\Google\Chrome\Application\chrome.exe')
_WinWaitActivate("ChromeDisclaimer.htm - Google Chrome","")
MouseClick("left",351,71,1)
Send("www.angus.org{ENTER}")
_WinWaitActivate("American Angus Association - Google Chrome","")
 
 
$myCow = InputBox("Question", "What is your cow's number?")
$myBull = InputBox("Question", "What is your bull's number?")
 
MouseClick("left",1536,161,1)
;Send("17914484{ENTER}")
;Send($myCow{ENTER})
;Send("$myCow{ENTER}")
Send($myCow & "{ENTER}")
 
sleep(3000)
 
_WinWaitActivate("EPD Details - Google Chrome","")
MouseMove(297,747)
MouseDown("left")
MouseMove(1614,982)
MouseUp("left")
 
sleep(3000)
Send("{CTRLDOWN}c{CTRLUP}")
 
sleep(3000)
 
Run('C:\Program Files (x86)\Microsoft Office\Office16\excel.exe')
 
sleep(3000)
;_WinWaitActivate("Program Manager","")
;MouseClick("left",4297,1053,1)
_WinWaitActivate("Book1 - Excel","")
MouseClick("left",92,285,1)
Send("{CTRLDOWN}v{CTRLUP}")
 
_WinWaitActivate("Book1 - Excel","")
MouseClick("left",1787,23,1)
_WinWaitActivate("EPD Details - Google Chrome","")
MouseClick("left",1544,165,1)
Send($myBull & "{ENTER}")
 
sleep(3000)
 
_WinWaitActivate("EPD Details - Google Chrome","Chrome Legacy Window")
MouseClick("left",1527,160,1)
MouseMove(307,736)
MouseDown("left")
MouseMove(1709,972)
MouseUp("left")
 
Sleep(3000)
Send("{CTRLDOWN}c{CTRLUP}")
Sleep(3000)
 
;_WinWaitActivate("Program Manager","")
;MouseClick("left",4230,1052,1)
_WinWaitActivate("Book1 - Excel","")
 
;_WinWaitActivate("Book1 - Excel","Collect and Paste 2.")
Sleep(3000)
 
 
MouseClick("left",88,693,1)
Sleep(3000)
 
Send("{CTRLDOWN}v{CTRLUP}")

QUOTE=John_w;4909232]As Kenneth said, try surrounding the exe file name and path with double quotes, like this:

AngusAppPath = Chr(34) & "S:\James Jech Stuff\Program 6 - add vba - Copy.exe" & Chr(34) 'Your path here

CreateObject("Wscript.Shell").Run AngusAppPath, 1, True '0=hide window, 1=show window; True = wait on return

Also delete the On Error Resume Next as that hides any run-time errors. You want to know about any errors so you can program accordingly.

Please use CODE tags - click the # icon in the message editor.[/QUOTE]