Excel VBA: Functions not running synchronously

Question

I am writing a macro on button click. On this button click, I have three functions to run one after another.

Code is:

Sub submit()
    ActiveWorkbook.Save
    Call RUnCode
    Call copy_paste_sheet
    Call cumulative_percent
End Sub

Code for RUnCode is:

Private Sub RUnCode()
    Dim pyPrgm As String, pyScript As String
    pyPrgm = "python "
    pyScript = "C:\Users\Kishan\Desktop\script.py"
    Call Shell(pyPrgm & pyScript, vbMinimizedNoFocus)
End Sub

Function RUnCode is running a python script through command line. It is takes some time to run, and I need data from python script for copy_paste_sheet and cumulative_percent function (Both functions are simple excel sheet manipulations).

But problem is: before the RUnCode is over, other two functions are running, thereby leading to processing the wrong data.

Can you help me with this. Thanks in advance.

Look into wait commands in VBA. I believe there is a way you can make the code wait until the script has completed before running the next portion — RGA, Jul 08 '16 at 07:19
Also check out using `WshShell` https://msdn.microsoft.com/en-us/library/aew9yb99 and http://stackoverflow.com/questions/8902022/wait-for-shell-to-finish-then-format-cells — Robin Mackenzie, Jul 08 '16 at 07:50

score 0 · Answer 1 · answered Jul 08 '16 at 13:42

Try this instead:

Private Sub RunCode()

    Dim objShell As Object
    Dim strCmd As String
    Dim lngErrorCode As Long

    strCmd = "python C:\Users\Kishan\Desktop\script.py"
    Set objShell = CreateObject("WScript.Shell")
    lngErrorCode = objShell.Run(strCmd, 1, True)
    Debug.Print lngErrorCode

End Sub

Excel VBA: Functions not running synchronously

1 Answers1