Run a Python script via Excel/VBA

Question

I've been trying to find a solution to this problem for ages. I have some python scripts that need to be updated regularly and would like to be able to use a macro to accomplish this. Right now I double click the files and they execute via Shell without a problem.

Sub RunPyScript()

Dim Ret_Val As Variant
Dim command As String

command = Chr(34) & "C:\Users\Jon Doe\python.exe" & Chr(34) & " " & Chr(34) & "C:\Users\Jon Doe\" & "\Roto.py" & Chr(34)
Ret_Val = Shell(command, vbNormalFocus)

End Sub

When I attempt to run the above macro, it looks as though it will run the same as when I double click, but Shell exits before the script is executed (I think that's the issue, not positive). If anyone could help me out I'd really appreciate it.

Answer 1

Dim objShell
'Dim command             'use this declaration type for VBS (Script), instead of the direct declaration like as "As String"
Dim command as String   'use this declaration type for VBA (Access/Excel)

command = Chr(34) & "C:\Users\John Doe\python.exe" & Chr(34) & " " & Chr(34) & "C:\Users\John Doe\" & "\roto.py" & Chr(34)
Set objShell = CreateObject("WScript.Shell")

objShell.Run command, 1, True

'Settings for WindowStyle:
'     0 Hide the window (and activate another window.)
'     1 Activate and display the window. (restore size and position) Specify this flag when displaying a window for the first time.
'     2 Activate & minimize.
'     3 Activate & maximize.
'     4 Restore. The active window remains active.
'     5 Activate & Restore.
'     6 Minimize & activate the next top-level window in the Z order.
'     7 Minimize. The active window remains active.
'     8 Display the window in its current state. The active window remains active.
'     9 Restore & Activate. Specify this flag when restoring a minimized window.
'    10 Sets the show-state based on the state of the program that started the application.

BTW, you can simply use 2x quotation marks for inserting quotes into a string variable, as I used them in my previous source code. For example StrVariable="""" gives the result ". So then the string for command-line will be:

command = """%USERPROFILE%\python.exe"" ""%USERPROFILE%\roto.py"""

where the result is:

"C:\Users\John Doe\python.exe" "C:\Users\John Doe\roto.py"