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!

Could your script read the filename from the pre-made excel? — rcriii
– rcriii, Commented Aug 17, 2020 at 20:41

QHarr · Accepted Answer · 2020-08-18 12:14:16Z

5

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

edited Aug 18, 2020 at 12:14

answered Aug 17, 2020 at 22:09

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim Williams Over a year ago

? space between PythonExe and PythonScript ?

QHarr Over a year ago

@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 Over a year ago

@QHarr - I think the embedded double quote is acting as a command line separator.

QHarr Over a year ago

@TimWilliams Good thinking there

YasserKhalil Over a year ago

@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?

Collectives™ on Stack Overflow

Pass Python Argument Through VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related