1

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:

  1. both users have identical non-admin access, yet it does not execute for one of them producing no error message (but cmd opens)
  2. network path does not have spaces
  3. the same line runs just fine from cmd directly
  4. 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
  5. full anaconda3 reinstall did not fix the problem
  6. 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 ...

gregV
  • 987
  • 9
  • 28
  • 1
    Already tried a simpler Python script like something as `with open("out.txt", "w") as f; f.write("Test")`? – Michael Butscher Sep 04 '18 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 with`python "\\acntnyc039\dept\HGS\Bob\test_syst_arg2.py"` . still confused – gregV Sep 04 '18 at 21:23
  • 1
    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 Sep 04 '18 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: https://stackoverflow.com/questions/51947269/how-does-one-capturing-shell-output-with-shell-run-instead-of-shell-exec-in-vba – Lucas Raphael Pianegonda Sep 05 '18 at 09:32
  • @MichaelButscher fantastic, after redirecting `sys.stderr` produced `'ImportError: Missing required dependencies ['numpy']'`. make it an answer if you need points – gregV Sep 05 '18 at 20:17

1 Answers1

0

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!')
gregV
  • 987
  • 9
  • 28