Running Python in VBA where there are spaces in execution path

I wrote a Python script to perform calculations which I want to run via Excel VBA and display the results in Excel. To be usable for different users I am trying to write a code that will generate the paths to the Python execution and Python scripts based on the usernames they enter since that is the only thing that will change in the path.

Some people have a space in their username which causes problems when trying to run Python in the command prompt.

There are similar questions but I have not found a solution. I tried adding triple double quotations at the beginning and end of the execution path e.g.

"""C:UsersClaire Watsonanaconda3python.exe"""

The result in the command prompt was something like ‘C:UsersClaire’ is not recognized. This does however work for the Python script path.

I also saw that adding a ^ before the space could help e.g.

"""C:UsersClaire^ Watsonanaconda3python.exe"""  

or

"C:UsersClaire^ Watsonanaconda3python.exe"  

The result is that command prompt says C:UsersClaire: can’t open file ‘Watsonanaconda3python.exe: [Errno 2] No such file or directory.

I also tried removing some quotes but then it states that there is a syntax error or it cannot find the path.

My code:

Dim objShell As Object
Dim pythonexe As String
Dim pythonscript As String
Dim Username As String

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

Username = "Some name with or without spaces" 'dependent on the user input

pythonscript = """C:Users" & Username & "........py"""

If InStr(Username, " ") > 0 Then ' Checks to see if there are spaces in username

    pythonexe = """C:Users" & Username & "anaconda3python.exe"""
    
    objShell.Run "cmd /k" & " " & pythonexe & " " & pythonscript
Else

     pythonexe = "C:Users" & Username & "anaconda3python.exe"
    
     objShell.Run "cmd /k" & " " & pythonexe & " " & pythonscript   ' This section gives no errors
     
End If

Answer

cmd.exe has pretty nasty quoting rules. You should, however, be able to quote both the Python interpreter path and the script in such a way that it will work consistently whether or not there is a space in the username. Try something like this:

Dim objShell As Object
Dim pythonexe As String
Dim pythonscript As String
Dim Username As String
Dim cmd As String
'Double-quote character
Dim dq As String: dq = Chr(34)

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

Username = "Some name with or without spaces" 'dependent on the user input

pythonexe = dq & "C:Users" & Username & "anaconda3python.exe" & dq
pythonscript = dq & "C:Users" & Username & "........py" & dq

cmd = "cmd /k " & dq & pythonexe & " " & pythonscript & dq
Debug.Print "Running command (" & cmd & ")"
objShell.Run cmd

The correctly-quoted command should look something like this (the Debug line prints it to the Immediate Window for debugging purposes):

cmd /k ""C:UsersClaire Watsonanaconda3python.exe" "C:UsersClaire Watson.........py""