Run and execute python script from VBA
I am trying to run a python script from my VBA module. I've tried almost every example I've seen on the internet, so I had no luck. In the VBA module I also run the .bat file and it works fine:
batchname = "U:\Backup Bat File.bat"
Shell batchname, vbNormalFocus
Next, I need to run a python script, which is in the same folder as the excel file. Right now I am trying this:
Dim Ret_Val
Dim args
args=Activeworkbook.Path & "\beps_output.py"
Ret_Val = Shell("C:\python34\python.exe" & args, vbNormalFocus)
This is not a bug, but nothing happens. I am a bit confused about what "Ret_Val" (return value?) Means here and why it doesn't work.
source to share
Try adding a space between the exe program and the file:
Ret_Val = Shell("C:\python34\python.exe " & args, vbNormalFocus)
In addition, since Shell is a function that returns a value (variant is double) with the specified arguments, the return value can be contained in a variable, here as you specify with Ret_Val. Then you can add conditional logic and error handling using this value.
source to share
This works on my PC (Win 7 x64, python 2.7.9):
Sub runpython()
Dim Ret_Val
Dim args As String
args = "W:\programming\python\other_py\sqrt.py"
Ret_Val = Shell("C:\Program Files (x86)\python27\python.exe" & " " & args, vbNormalFocus)
If Ret_Val = 0 Then
MsgBox "Couldn't run python script!", vbOKOnly
End If
End Sub
Ret_Val
will be nonzero if the call succeeds, namely the process ID of the running command. Note that the command will run asynchronously, meaning the VBA code will continue faster than the external command completes.
source to share