Running python script from VBA

Question

the below code is executing simple python script from Windows excel VBA Shell on 1 machine, but not on the other.

 Sub RunIt()
    CreateObject("wscript.shell").Run "python.exe " & """\\acntnyc039\dept\HGS\Bob\test_syst_arg2.py""", 1, True
    'same as
    Shell "python.exe ""\\acntnyc039\dept\HGS\Bob\test_syst_arg2.py""", 1
End Sub

corresponding python:

import pandas as pd
import sys
path = r'\\acntnyc039\dept\HGS\Bob\test\test.csv'

raw_data = {'first_name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'], 
        'last_name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze'], 
        'age': [42, 52, 36, 24, 73], 
        'preTestScore': [4, 24, 31, 2, 3],
        'postTestScore': [25, 94, 57, 62, 70]}
df = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age',
                                       'preTestScore', 'postTestScore'])
df.to_csv(path)

i've read all similar threads and checked the following:

both users have identical non-admin access, yet it does not execute for one of them producing no error message (but cmd opens)
network path does not have spaces
the same line runs just fine from cmd directly
python is found when typing python from cmd, but still tried replacing python with python.exe along with full path to python.exe in VBA string above to no avail
full anaconda3 reinstall did not fix the problem
shell seems to be working fine on non-python code as in:

Shell "notepad.exe ""\\acntnyc039\dept\HGS\Bob\test\test.csv""", 1

i'm really running out of ideas here ...

Already tried a simpler Python script like something as with open("out.txt", "w") as f; f.write("Test")? — Michael Butscher
– Michael Butscher, Commented Sep 4, 2018 at 20:39
wow, the simpler code ran fine! does it mean it does not see pandas? but it was running fine from command line directly withpython "\\acntnyc039\dept\HGS\Bob\test_syst_arg2.py" . still confused — gregV
– gregV, Commented Sep 4, 2018 at 21:23
At the very beginning (above other imports) of original code you could place import sys; sys.stderr = open('err.txt', 'w'); sys.stdout = open('out.txt', 'w');. This should redirect error messages and show what goes wrong. — Michael Butscher
– Michael Butscher, Commented Sep 4, 2018 at 21:53
My explaination is this: VBA calls the shell and runs python from the shell and if python runs into any error you will not see errormessages since VBA is not recieving the messages and reading them out somewhere. I have tried to do this but have not found a conveniet solution. My suggestion: If possible debug the script in another enivornment before calling it from VBA. If your sure that everything runs fine, try again. Here my post on this topic: stackoverflow.com/questions/51947269/… — Lucas Raphael Pianegonda
– Lucas Raphael Pianegonda, Commented Sep 5, 2018 at 9:32
@MichaelButscher fantastic, after redirecting sys.stderr produced 'ImportError: Missing required dependencies ['numpy']'. make it an answer if you need points — gregV
– gregV, Commented Sep 5, 2018 at 20:17

gregV · Accepted Answer · 2022-01-14 04:14:35Z

here is the code in VBA:

Dim wshShell As Object
Dim py_exe As String
Dim script_path As String, script_name As String
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1

py_exe = "python.exe"
script_name = "write.py"
script_path = Application.ThisWorkbook.Path & "\" & script_name

Set wshShell = CreateObject("WScript.Shell")
wshShell.Run py_exe & " " & script_path, windowStyle, waitOnReturn

might need triple quotes """if spaces in the path"""

here is the most useful part (VBA code to check for errors via executing the bat):

Dim errorCode As Long  'to check via bat
errorCode = wshShell.Run("cmd.exe /C C:\code\yourpath\BaT.bat", windowStyle, waitOnReturn)

Here is the .bat itself:

:: this is to keep cmd open after run, /k
start cmd.exe /k ""python" "C:\code\yourpath\write.py""

and finally this is a sample python script write.py used above. Just be mindful of specifying full paths as it might run fine from cmd or IDE but not from wscript.Shell

from pathlib import Path
import sys
# directory of main and all supplementary files such as err.txt and .png
file_dir = Path(__file__).parent.absolute()

sys.stderr = open(file_dir / 'err.txt', 'w')
sys.stdout = open(file_dir / 'out.txt', 'w')

with open(file_dir / 'readme.txt', 'w') as f:
    f.write('Hello Peter!')

Collectives™ on Stack Overflow

Running python script from VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related