Excel VBA Macro contains File Path with Spaces

Question

I have a working macro that runs a Python script. I need to move the folder to my OneDrive which includes a space in the file path. I've looked at other examples and tried putting triple quotes around the file paths, but it's still not working.

Macro that works when there are no spaces in the file path is below.

Sub runpython3()
    Dim Ret_Val
    Dim args As String

    'Change directory to current directory and save Workbook
    folder_path = Application.ThisWorkbook.Path
    ChDir (folder_path)
    ThisWorkbook.Save

    'PythonExe and script path.  
    args = folder_path & "\job_sim_2.py"
    PythonExe = folder_path & "\..\venv\scripts\python.exe"

    'Run Python script
    Ret_Val = Shell("cmd /k " & PythonExe & " " & args, vbNormalFocus)
    If Ret_Val = 0 Then
       MsgBox "Couldn't run python script!", vbOKOnly
    End If
End Sub

Macro that contains space in file path. The triple quotes did not fix the issue.

Sub runpython3()
    Dim Ret_Val
    Dim args As String

    'Change directory to current directory and save Workbook
    folder_path = Application.ThisWorkbook.Path
    ChDir (folder_path)
    ThisWorkbook.Save

    'PythonExe and script path.  NOTE: To use folder_path, can not contain spaces in path
    args = """C:\Users\smalik\OneDrive - NI\Job-Sim\source\job_sim_2.py"""
    PythonExe = """C:\Users\smalik\OneDrive - NI\Job-Sim\venv\Scripts\python.exe"""

    'Run Python script
    Ret_Val = Shell("cmd /k " & PythonExe & " " & args, vbNormalFocus)
    If Ret_Val = 0 Then
       MsgBox "Couldn't run python script!", vbOKOnly
    End If
End Sub

I get 2 errors when running the Macro

Macro fails on ChDir (folder_path). If I comment out this line I get the second error
CMD errors on the file path due to the space.
enter image description here

`cmd /k` executes a [string](https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/cmd) so try `Ret_Val = Shell("cmd /k """ & PythonExe & " " & args & """", vbNormalFocus)` — CDP1802, Apr 20 '23 at 14:48
@CDP1802, that worked!! I don't understand it, but it worked. Is there documentation on the quotations so I can understand the reasoning behind why this works? Also, do you know how to fix the issue with ChDir not working? Does this also have to do with the space in the file path? I need it to change the directory or else it tries to run the Python script from the wrong directory and then can't find the Excel file called out in the script. — Stacy Malik, Apr 20 '23 at 15:37
@TimWilliams, I had looked at many examples, but hadn't found that one yet. Thank you! — Stacy Malik, Apr 20 '23 at 15:40
That is clearly the issue. It's returning a URL instead of a directory path. I think I just stumbled on a Stack Overflow addressing this, though I don't really understand the solution. https://stackoverflow.com/questions/46346567/thisworkbook-fullname-returns-a-url-after-syncing-with-onedrive-i-want-the-file — Stacy Malik, Apr 20 '23 at 17:53
@CDP1802, I found an article on how to get the full name of the spreadsheet converted from URL to local directory, but I need to remove the filename to get just the file path. Luckily the filename is always the same \Job_Sim_Inputs.xlsm. How can I remove that from the FullName path? — Stacy Malik, Apr 20 '23 at 18:21
`Left(fullname,Len(fullname) - Len("\Job_Sim_Inputs.xlsm"))` — CDP1802, Apr 20 '23 at 18:50
@CDP1802, thank you for all your help. I've made it past the macro issues and now have a permissions issue reading the Excel spreadsheet in the Python script that I didn't get before moving the file to OneDrive. (sigh) If you add your first comment as an answer I will accept it as the correct answer! — Stacy Malik, Apr 20 '23 at 19:14

Excel VBA Macro contains File Path with Spaces

0 Answers0