How to use Xlwings RunPython for file name with spaces

Question

I am trying to run a python function called comments() from vba and getting an error with the following code due to spaces in the file name:

Sub notes()
Dim filename As String

filename = "Data Entry - 1 Jan 2020 to 31 Dec 2020"

RunPython ("import " & filename & ";" & filename & ".comments()")
End Sub

The python file has the same name as the excel file and was created with the command xlwings quickstart "Data Entry - 1 Jan 2020 to 31 Dec 2020" --standalone

How can I get this working?

Answer 1

Felix Zumstein wrote in an answer somewhere that RunPython literally runs Python within VBA. Using this knowledge, it is easy to recognise that we then use Python commands within the parentheses: import file; file.function(), with ; separating the lines (as can be used in Python when writing on one line): importing a script or package, then calling a function/module within that. There are limitations to this of course, as RunPython cannot be used to return a value - so ActiveSheet.Range("A1").value = RunPython "4*4" would not work.

This does mean, however, that standard packages can be imported within this statement. importlib is a standard package, and can be used to import functions/modules. It is especially useful for when the file's/script's name contains spaces.

Because the string within the parentheses of the function importlib.import_module() requires quotation marks, (as we are still working in VBA) single or double quotes can be used. It is easier to use single quotes, but I have used both in the code below.

If my script name is "Test Script", and the function I want to call is "testing":

Sub test()

    ' Double quotation marks
    RunPython "import importlib; importlib.import_module(""""Test Script"""").testing()"
    ' Single quotation marks 
    RunPython "import importlib; importlib.import_module('Test Script').testing()"

End Sub

With your specific inputs:

Sub notes()

    ' Double quotation marks
    RunPython ("import importlib; importlib.import_module(""""Data Entry - 1 Jan 2020 to 31 Dec 2020"""").comments()")
    ' Single quotation marks
    RunPython ("import importlib; importlib.import_module('Data Entry - 1 Jan 2020 to 31 Dec 2020').comments()")

End Sub

As another example of running code within RunPython:

Sub delete_file()

    RunPython ("import os; os.remove(r'C:\Users\<username>\file_name.txt')")

End Sub