How to call python script on excel vba

Question

trying to call a python script on Vba and I am a newb  I tried converting the main script to an exe using py2exe and then calling it from VBA  shell  but the main script calls other scripts therefore it becomes complicated and I messed it up  my exe is not functional   Besides  the the main script is a large file and I do not want to revise it a lot    Bottomline  is there a way to call the main script from excel vba  without converting the script to an exe file    So far  I tried    RetVal   Shell  C  python27 python exe    amp   import    amp   C      amp   MainScriptFile     It starts python exe but does nothing else  Then I tried   RetVal   Shell  C  Windows System32 cmd exe    amp   python    amp   C   Python27  hello py     It starts command prompt but does not even start python   P S  I checked all the related questions in the forum  they do not solve my prob

User · Accepted Answer

Try this   RetVal   Shell   lt full path to python exe gt     amp    lt full path to your python script gt      Or if the python script is in the same folder as the workbook  then you can try    RetVal   Shell   lt full path to python exe gt     amp  ActiveWorkBook Path  amp     lt python script name gt      All details within  lt  gt  are to be given   lt  gt  - indicates changeable fields  I guess this should work  But then again  if your script is going to call other files which are in different folders  it can cause errors unless your script has properly handled it  Hope it helps

User · Answer

To those who are stuck wondering why a window flashes and goes away without doing anything  the problem may related to the RELATIVE path in your Python script  e g  you used       Even the Python script and Excel Workbook is in the same directory  the Current Directory may still be different  If you don t want to modify your code to change it to an absolute path  Just change your current Excel directory before you run the python script by   ChDir ActiveWorkbook Path   I m just giving a example here  If the flash do appear  one of the first issues to  check is the Current Working Directory

User · Answer

To those who are stuck wondering why a window flashes and goes away without doing anything the python script is meant to do after calling the shell command from VBA  In my program  Sub runpython    Dim Ret Val args      F  my folder helloworld py    Ret Val   Shell  C  Users username AppData Local Programs Python Python36 python exe    amp       amp  args  vbNormalFocus  If Ret Val   0 Then    MsgBox  Couldn t run python script    vbOKOnly End If End Sub   In the line args      F  my folder helloworld py     I had to use triple quotes for this to work  If I use just regular quotes like  args    F  my folder helloworld py  the program would not work  The reason for this is that there is a space in the path  my folder   If there is a space in the path  in VBA  you need to use triple quotes

User · Answer

You can also try ExcelPython which allows you to manipulate Python object and call code from VBA

User · Answer

This code will works    your path  ActiveWorkbook Path  amp    your python file py      Shell  RunDll32 Exe Url Dll FileProtocolHandler    amp  your path  vbNormalFocus   ActiveWorkbook Path return the current directory of the workbook  The shell command open the file through the shell of Windows

User · Answer

Try this   retVal   Shell  python exe  lt full path to your python script gt    vbNormalFocus    replace  lt full path to your python script gt  with the full path

User · Answer

There are a couple of ways to solve this problem  Pyinx - a pretty lightweight tool that allows you to call Python from withing the excel process space http   code google com p pyinex   I ve used this one a few years ago  back when it was being actively developed  and it worked quite well  If you don t mind paying  this looks pretty good  https   datanitro com product html  I ve never used it though    Though if you are already writting in Python  maybe you could drop excel entirely and do everything in pure python   It s a lot easier to maintain one code base  python  rather than 2  python   whatever excel overlay you have    If you really have to output your data into excel there are even some pretty good tools for that in Python   If that may work better let me know and I ll get the links

User · Answer

I just came across this old post   Nothing listed above actually worked for me   I tested the script below  and it worked fine on my system   Sharing here  for the benefit of others who come here after me   Sub RunPython    Dim objShell As Object Dim PythonExe  PythonScript As String      Set objShell   VBA CreateObject  Wscript Shell        PythonExe      C  your path Python Python38 python exe        PythonScript    C  your path from vba py       objShell Run PythonExe  amp  PythonScript  End Sub

User · Answer

ChDir  quot  quot  was the solution for me  I use vba from WORD to launch  a python3 script

[python] How to call python script on excel vba?

Examples related to python

Examples related to excel

Examples related to vba

Examples related to shell

Examples related to py2exe