Call Python Script from VBA

Question

I'm having a problem calling python scripts from vba in Excel. I read other threads that addressed the same problem, but when I run the code, a Python screen flashes and then disappears. FYI, I downloaded python 3.6.5 for Windows 10 and added it to PATH. Can someone let me know why the screen flashes and what I can do to solve it? Thanks

Sub Run_python()

Dim Ret_Val
Dim args As String

args = "C:\Users\opera\AppData\Local\Programs\Python\Python36\Tools\scripts\db2pickle.py"
Ret_Val = Shell("C:\Users\opera\AppData\Local\Programs\Python\Python36\python.exe" & " " & args, vbNormalFocus)

If Ret_Val = 0 Then
MsgBox "Couldn't run python script!", vbOKOnly
End If

End Sub

can you add some input() statement at the end of your script? when the script finishes the window closes. — Jean-François Fabre
– Jean-François Fabre ♦, Commented Jun 11, 2018 at 20:33
for anyone else that stumbles on this page, xlwings is a good extension that can simplify the manipulation of xl sheets with python functions — Attack68
– Attack68, Commented Jun 11, 2018 at 20:49
@Jean-FrançoisFabre what exactly would the input() statement look like? — efly
– efly, Commented Jun 12, 2018 at 12:18

dbmitch · Accepted Answer · 2018-06-11 21:03:37Z

0

The screen flashes and returns quickly because the program either failed or ran that fast to completion

Have you tried to run it from the command line to see what is happening?

If that runs fine, you can force the command window to stay open so you can read what's happening when you use python from the Shell command

Call Shell("cmd.exe /S /K" & "C:\Users\opera\AppData\Local\Programs\Python\Python36\python.exe" & " " & args, vbNormalFocus)

/S      Modifies the treatment of string after /C or /K 
/C      Carries out the command specified by string and then terminates 
/K      Carries out the command specified by string but remains

answered Jun 11, 2018 at 21:03

dbmitch

5,4064 gold badges27 silver badges39 bronze badges

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

2 Comments

efly Over a year ago

I replaced the Ret_val=... line with your solution, and it brought up the command window, but I also get the message saying "Couldn't run python script" because apparently my return value was 0. Does that matter at all, and if not I can get rid of the Ret_Val variable?

dbmitch Over a year ago

You should be able to check %ERRORLEVEL% on its return. You don't need return value unless you are tracking how your script is exiting

Priya Srivatsa · Accepted Answer · 2020-04-07 03:25:22Z

0

I tried the below as suggested:

Dim RetVal
args = """C:\Users\something\Desktop\md\test.py"""
RetVal = Shell("C:\Python37\python.exe " & " " & args)
If RetVal = 0 Then
   MsgBox "Couldn't run python script!", vbOKOnly
End If

After running the script the command window was just flashing. So, I just added input('Press any key to exit') statement as the last line of my python script and now the window stays.

answered Apr 7, 2020 at 3:25

Priya Srivatsa

238 bronze badges

Collectives™ on Stack Overflow

Call Python Script from VBA

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related