0

I don't usually code in VBA, but need to create a VBA macro from which I can run a .bat file that executes a python script. I have been able to do this, however since the program runs outside the excel environment, I need to determine when the .bat file has completed, and return a message to the excel environment. This is what I have so far:

Shell ("CMD.EXE /c c:\IndexTrader\run_file\index_trader.bat")

Once the above .bat file runs, how can I print a message to excel environment that shows that the .bat file has executed. If I run sequential code after the .bat file command (see code above), since the .bat file is executed outside the excel environment, any sequential code that is run after this command will run even though the .bat file has not completed. Is there a way to output a message or parameter that shows that the .bat file has executed, and then only execute any sequential code in VBA.

Community
  • 1
  • 1
blala
  • 47
  • 2
  • 7
  • 1
    1) Have `bat` file create a file (i.e. - .txt) after execution. Then you can use `filesystemobject` to check if that file exists before moving onto rest of code. Bat file can also destroy that file at beginning of code 2) If you know the python script will only take max 10 seconds or so you can use VBA `Application.Wait` to hold execution, or `Sleep` API. – Scott Holtzman Oct 17 '17 at 15:39
  • @ScottHoltzman method definitely works (tried it before), but just another idea, how about have the bat program set a system environment variable, then have the vba wait until the `environ` variable set to a specific value. Never tried this, but i think is the same as creating a file. – Rosetta Oct 17 '17 at 15:50
  • @Rosetta - its worth the experiment to try it out :) – Scott Holtzman Oct 17 '17 at 15:52
  • @ScottHoltzman seems to me my idea is just a dead end... https://stackoverflow.com/questions/171588/is-there-a-command-to-refresh-environment-variables-from-the-command-prompt-in-w – Rosetta Oct 17 '17 at 16:30

1 Answers1

1

Full disclosure: ever so slightly adapted from Return result from Python to Vba which was itself influenced by Capture output value from a shell command in VBA?

By the way, if all you want to do is execute a python script from VBA then go here.

Essentially you need to create a WshShell object and call its WshShell.Exec. This will give you a handle on the process that is running your batch file and can even be used to extract data from stdOut, stdErr, etc.

The VBA code you need is:

Public Function runProcess(cmd As String) As String

    Dim oShell As Object
    Dim oExec As Object, oOutput As Object
    Dim s As String, sLine As String

    Set oShell = VBA.CreateObject("Wscript.Shell")    

    Set oExec = oShell.Exec(cmd)
    Set oOutput = oExec.StdOut

    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbNewLine
    Wend

    Set oOutput = Nothing: Set oExec = Nothing
    Set oShell = Nothing

    runProcess = s

End Function

You can invoke this as in your example by using:

Dim out_str As String
out_str = runProcess("c:\IndexTrader\run_file\index_trader.bat")
MsgBox "Script complete!"

The additional benefit that you get with this method is that you can capture that process output and therefore pass messages from your python script back to VBA/Excel.

Even if you ignore the returned value, you are guaranteed that the process is complete by the time control returns to the calling function.

If you're .bat file takes command line parameters you can use:

Dim out_str As String
Dim arg1 As String
arg1 = "6"
out_str = runProcess("c:\IndexTrader\run_file\index_trader.bat " & arg1)
MsgBox "Script complete!"
Linford Bacon
  • 251
  • 1
  • 6
  • Hi Linford, thank you so much this works perfectly. :-) – blala Oct 18 '17 at 07:23
  • Hi Linford, I have created an input function in python which requires a user to input a value. I tried to run the script again (from VBA), but it just causes excel to hang and shutdown. I believe this is because the program cannot run without the input (which its waiting for). If I run the python script from .cmd/python,I am prompted for the input and once entered the code continues to run. How can I do this from the VBA script you have provided above? – blala Oct 23 '17 at 08:06
  • Hi @user8689353 - what you are describing is an 'interactive' python script. If you want to keep this method you should ask a question about creating an interactive shell session from VBA. However, I would recommend that you change your python script so that it gets the required parameters from a command line argument, rather than from manual interaction. You can see how to do this [here](https://stackoverflow.com/questions/4033723/how-do-i-access-command-line-arguments-in-python) – Linford Bacon Oct 23 '17 at 10:29
  • Hi Linford thank you, you are a true expert and I appreciate your help. :-) The only change I made was passing the argument in the bat file, not in the **runProcess** function, then using **import sys** in python script to get the parameter value passed (arg1) – blala Oct 23 '17 at 13:03