Pass Python Argument Through VBA

Question

I have written a python script that needs to be called using VBA. I have written a python script that can parse through a pdf, store the data I need in variables, and write to a pre-made excel sheet to fill in the cells with those values. I have successfully been able to call that script and run it using the following code:

Sub RunPythonScript()
'Declare Variables
Dim objShell As Object
Dim PythonExe, PythonScript As String
'Create a new Object shell.
Set objShell = VBA.CreateObject("Wscript.Shell")
'Provide file path to Python.exe
'USE TRIPLE QUOTES WHEN FILE PATH CONTAINS SPACES.
PythonExe = """C:\Python38\python.exe"""
PythonScript = "C:\Users\matthew_vidovic\Documents\dwf-python\fedExPDF.py"
'Run the Python Script
objShell.Run PythonExe & PythonScript
End Sub

The final step for me is to be able to pass in a pdf file from VBA rather than in the python script. Currently, inside my python script, I open a pdf and store it as text with the following code

raw = parser.from_file('nameofmyfile.pdf')
pdfText = raw['content']

Rather than doing this, I want to be able to change the pdf file being analyzed from within vba, and not by changing the python script. Is there a way for me to pass an argument to the python script from vba, with the argument being the name of the pdf file. I need to be able to change the pdf file from vba and then the file be analyzed by the python script. Is something like this possible? Any help would be greatly appreciated. Cheers!

• rcriii: Could your script read the filename from the pre-made excel?

Answer 1

Make sure you import sys and read the appropriate arg within the script then in VBA call with space then the argument e.g.

test.py:

import sys
with open(f'C:/Users/User/OneDrive/Desktop/{sys.argv[1]}', 'w') as f:
 f.write(b'some text')

VBA:

 Sub RunPythonScript()
 
 'Declare Variables
 Dim objShell As Object
 Dim PythonExe, PythonScript As String
 
 'Create a new Object shell.
 Set objShell = VBA.CreateObject("Wscript.Shell")
 
 'Provide file path to Python.exe
 'USE TRIPLE QUOTES WHEN FILE PATH CONTAINS SPACES.
 PythonExe = """C:\Users\User\AppData\Local\Programs\Python\Python38\python.exe"""
 PythonScript = "C:\Users\User\OneDrive\Desktop\test.py"
 
 'Run the Python Script
 objShell.Run PythonExe & Chr$(32) & PythonScript & Chr$(32) & "Argument.txt"
 
 End Sub

• Tim Williams: ? space between PythonExe and PythonScript ?
• QHarr: @TimWilliams I would expect so however, in my test case when coding it, it ran. I think I will edit a space in, thank you, as that would be how I would expect to see it. Interesting that it works in both cases.
• Tim Williams: @QHarr - I think the embedded double quote is acting as a command line separator.
• QHarr: @TimWilliams Good thinking there
• YasserKhalil: @QHarr Is it possible to run the python script stored in a sheet cell (say A1 has the value print('Hello World') and I need to run the line from VBA?