Call python script from vba with wsh

Question

I need to call a python script from vba which works fine by using the shell.

Sub CallPythonScript()

        Call Shell("C:\Program Files (x86)\Python27\python.exe C:\Users\Markus\BrowseDirectory.py")

End Sub

But when I try using wsh (because of the wait funcionality) it just won't work anymore.

Sub CallPythonScript()

    Dim wsh As Object
        Set wsh = VBA.CreateObject("WScript.Shell")

    Dim myApp As String: myApp = "C:\Program Files (x86)\Python27\python.exe C:\Users\Markus\BrowseDirectory.py"
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1

    wsh.Run """"" & myApp & """"", windowStyle, waitOnReturn

End Sub

However, I used the same code at home and everything worked out just fine, with the difference that there weren't any blanks in the path. So naturally there must be something wrong with the blanks. Help is greatly appreciated.

Answer 1

The interpreter cannot possibly distinguish directories containing spaces from real arguments unless you protect the directories with quotes.

A workaround, which is actually a better solution, relies on the fact that Python associates .py extension with the installed python interpreter.

Just do this (like you would launch a .bat file):

Dim myApp As String: myApp = "C:\Users\Markus\BrowseDirectory.py"

and your code will work no matter where python is installed (and the problem with spaces vanishes)

This little test in excel worked for me and ran pycrust.py which happens to be in my system PATH:

Sub foo()
  Dim wsh As Object
  Set wsh = VBA.CreateObject("WScript.Shell")

    Dim myApp As String: myApp = "pycrust.py"
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1

    wsh.Run myApp, windowStyle, waitOnReturn

End Sub

(I had to simplify your wsh.Run line because it was not working, I suspect that the 256 quotes you added didn't do any good)

Answer 2

Did you verify that path takes you directly to the interpreter?

Try this,

Dim myApp As String: myApp = "C:\""Program Files (x86)""\Python27\python.exe C:\Users\Markus\BrowseDirectory.py"