2

EDIT: I made an error when posting below in saying that it worked when running from terminal (I must have tested earlier.) This problem was solved by using the python.exe program in the environment (env) folder pycharm installed instead of the stand alone installation I made originally.

EDIT #2: The problem is persisting again without having changed any of the VBA or python script. (still using the pycharm environment folder python.exe)

I have a VBA sub that creates a WScript.Shell object and then executes a python script which was working fine for the last few weeks. After continuing to build on the code in the python script (salesHist.py) the python script no longer runs correctly and produces an exit code "1". When running the script through pyCharm or console the script fully executes correctly with exit code 0.

I've tried some different variations of the code which are included below. Directories for python.exe and salesHist.py are both correct and do not contain any spaces (which I know is a common error.)

Also references added: Visual Basic for Applications, Microsoft Excel 16.0 Object Library, OLE Automation, Microsoft Office 16.0 Object Library, Microsoft HTML Object Library, Microsoft Scripting Runtime, Microsoft XML, v3.0, Windows Script Host Object Model

Sub RunPythonScript(pyScript As String)

'Declare varables
Dim objShell As Object
Dim PythonExe, PythonScript As String
Dim waitOnReturn As Boolean, windowStyle As Integer, retVal As Long
waitOnReturn = True
windowStyle = 0

'Create new object shell
Set objShell = VBA.CreateObject("WScript.Shell")
'Tried Set objShell CreateObject("WScript.Shell")

'Provide the file path to the Python Exe
PythonExe = "C:\Users\steve.levy\AppData\Local\Programs\Python\Python37-32\python.exe"
'PC1: C:\Users\Steven\AppData\Local\Programs\Python\Python37-32\python.exe
'PC2: C:\Users\steve.levy\AppData\Local\Programs\Python\Python37-32\python.exe
'make sure you use triple quotes if there is a space in the file path name. single quotes are ok if not

'Procide the file path to the Python Script
PythonScript = "C:\Users\steve.levy\Documents\elberon\api\" & pyScript
'PC1: C:\Users\Steven\Documents\api\
'PC2: C:\Users\steve.levy\Documents\elberon\api\
Debug.Print (PythonExe)
Debug.Print (PythonScript)

'Run the Python Script
'Tried: Call objShell.Run(PythonExe & " " & PythonScript, 0, True)
'Tried: retVal = objShell.Run(PythonExe & " " & PythonScript, 0, True)
retVal = objShell.Run("C:\Users\steve.levy\AppData\Local\Programs\Python\Python37-32\python.exe C:\Users\steve.levy\Documents\elberon\api\salesHist.py", 0, True)

If retVal = 0 Then
    'Do Nothing
Else
    MsgBox "Script could not run. Program exited with error code " & retVal & "."
End If

End Sub

Sub pyscr()
    Call executePython.RunPythonScript("salesHist.py")
End Sub

Program exists and message box appears: "Script could not run. Program exited with error code 1."

slny06
  • 67
  • 11
  • First you need to grab the output of the Python call. The exit code alone is not helpful. Read this thread (https://stackoverflow.com/q/32492879/18771) and adapt your VBA code accordingly. Look at both stdout and stderr for your Python process. There will be some kind of meaningful error message. – Tomalak Sep 09 '19 at 15:18
  • I read through that thread (and the corresponding MSDN documentation) but can't get this to work. I keep getting errors surrounding the WScript.Sleep line. The variable isn't defined (or even when it is its not set (im not sure what I would set it to.) Here is the MSDN example : ```vba Dim WshShell, oExec Set WshShell = CreateObject("WScript.Shell") Set oExec = WshShell.Exec("C:\Users\steve.levy\Documents\elberon\venv\Scripts\python.exe C:\Users\steve.levy\Documents\elberon\api\salesHist.py") Do While oExec.Status = 0 WScript.Sleep 100 Loop WScript.Echo oExec.Status ``` – slny06 Sep 09 '19 at 17:58
  • No, there is no WScript.Sleep in VBA, that's exclusive to VBScript. I recommend the "DoEvents() in a While Loop" approach as a substitute. See https://stackoverflow.com/a/2737726/18771 – Tomalak Sep 09 '19 at 18:07

0 Answers0