44

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 " & "import " & "C:\\" & "MainScriptFile")

It starts python.exe but does nothing else. Then I tried:

RetVal = Shell("C:\Windows\System32\cmd.exe " & "python " & "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.

John Smith
  • 7,243
  • 6
  • 49
  • 61
Ege Ozlem
  • 903
  • 2
  • 9
  • 8
  • 1
    If you wrap the script in a class then it is callable from VBA using COM, http://exceldevelopmentplatform.blogspot.com/2018/01/calling-python-class-using-vba-with-com.html – S Meaden Apr 17 '18 at 09:49
  • 1
    Possible duplicate of [Calling python script from excel/vba](https://stackoverflow.com/q/3567365/608639), [How to call python script on excel vba?](https://stackoverflow.com/q/18135551/608639), [Is there a way to call a Python code in Excel-VBA?](https://stackoverflow.com/q/45410316/608639), [Run and execute a python script from VBA](https://stackoverflow.com/q/30531662/608639), etc. – jww Nov 15 '19 at 12:34
  • I just found a plugin: [PyWimVBA](https://github.com/ScadeBlock/PythonWIMVBA) that so unreputation athough it works well and newbie-friendly – Dile Aug 02 '23 at 13:13

13 Answers13

39

Try this:

RetVal = Shell("<full path to python.exe> " & "<full path to your python script>")

Or if the python script is in the same folder as the workbook, then you can try :

RetVal = Shell("<full path to python.exe> " & ActiveWorkBook.Path & "\<python script name>")

All details within <> are to be given. <> - 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.

SiHa
  • 7,830
  • 13
  • 34
  • 43
Rajgopal C
  • 812
  • 8
  • 9
  • I already tried these. It starts the python interpreter but does nothing else. I believe "shell" runs only the exe files and does not allow me to run my script. – Ege Ozlem Aug 09 '13 at 12:13
  • 4
    Sorry, I don't know what I did wrong previously. It worked now. Thanks a lot! I used: RetVal = Shell("C:\python27\python.exe C:\Python27\CallOptimizer.py") – Ege Ozlem Aug 09 '13 at 13:35
  • 1
    @EgeOzlem could you please share a sample of your python script, that you have executed from the VBA? and what it had returned to VBA call? – ggupta Sep 08 '17 at 06:14
  • 1
    Think it works, but the python program doesnt produce the expected output when run from the VBA macro. It works fine independently. Any idea if I can capture the output of the python program?? – Murtuza Husain Sep 12 '19 at 13:06
  • 1
    How can I call a function with passing arguments using the Shell? – Marcelo Gazzola Dec 03 '19 at 21:56
23

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 to this spot 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 & PythonScript
    
End Sub
ASH
  • 20,759
  • 19
  • 87
  • 200
  • 1
    Use triple double quote """Path""" to handle the spaces in folder path is much better, Thanks for the script. – WLiu Aug 06 '21 at 02:56
  • 1
    Triple quotes in Python Exe """path""" is indeed important to be noted. I got error because I didn't have triple quotes. But when I used triple quotes, it worked for me. – hbstha123 Feb 01 '23 at 23:39
  • Perfect! But how do we tell VBA to wait until hte Python program has finished its work? – tibibou Aug 21 '23 at 17:29
5

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.

Brad
  • 1,367
  • 1
  • 8
  • 17
  • Thanks for the suggestions. I can't drop excel entirely because this project is time sensitive, the python code is developed by someone else and I need to develop a simple interface for this code and I am not experienced in Python. The best way for me is using VBA. Maybe, in the future I may consider that. Thanks though. – Ege Ozlem Aug 09 '13 at 13:13
  • I can recommend DataNitro. Excellent integration. Bit pricey though – Joop Oct 03 '13 at 22:47
  • To any others willing to explore paid add-ins, **[xlwings](https://www.xlwings.org/)** is another option which I've used recently to reasonable effect (available with a 30-day trial). – Joshua Voskamp Dec 13 '22 at 20:12
2

This code will works:

 your_path= ActiveWorkbook.Path & "\your_python_file.py" 
 Shell "RunDll32.Exe Url.Dll,FileProtocolHandler " & your_path, vbNormalFocus

ActiveWorkbook.Path return the current directory of the workbook. The shell command open the file through the shell of Windows.

Pablo Vilas
  • 546
  • 5
  • 13
2

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 " & " " & 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.

Omi
  • 136
  • 1
  • 10
0

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

ehremo
  • 387
  • 2
  • 8
0

Try this:

retVal = Shell("python.exe <full path to your python script>", vbNormalFocus)

replace <full path to your python script> with the full path

Michael Dorner
  • 17,587
  • 13
  • 87
  • 117
T Roc
  • 11
  • should say RetVal = Shell("python.exe ",vbNormalFocus) and replace with the full path to your script. – T Roc Sep 22 '14 at 14:52
0

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.

Saul Han
  • 49
  • 3
0

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

Dede95
  • 41
  • 3
0

In my case, as I have my script in a different directory than my ms access document and, in that script I import variables from other scripts, I had to change the directory first. Then I can use correctly the shell function.

   ChDir "C:\Users\directory_where_the_script_is"
   Call Shell("C:\Users\...\python.exe " & "your_script_name.py", 1)
julioq
  • 56
  • 2
0

for me this not working RetVal = Shell(" " & ActiveWorkBook.Path & "<python script name>")

for me this :

RetVal = Shell("python3" & " " & "C:\a\b\c\xyz.py")

Dávid Đ
  • 1
  • This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/31075255) – Naeem Khan Feb 20 '22 at 05:23
0

I think above answers have a catastrophic problem:

after running the macro,cmd window would close automatically, instantly. In case output is not as expected you would have absolutely zero debug information. Finally I found a better way in which case, cmd window remains open and shows all the information (print of python) and error log.

code as below:

  Sub RunPythonScript()
        cwd = ActiveWorkbook.Path 'current working directory
        workbookName = ActiveWorkbook.Name
        pythonScriptName = "main.py"
        workbookNameWithPath = cwd & "\" & workbookName
        pythonScriptNameWithPath = cwd & "\" & pythonScriptName
        pythonExePath = "C:\Users\USERNAME\AppData\Local\Programs\Python\Python39\python.exe" ' change path as output of "where python"    
        
Shell "cmd.exe /k """"" & pythonExePath & """ """ & pythonScriptNameWithPath & """""", vbNormalFocus
    End Sub
Pawan Kumar
  • 114
  • 9
0

In case anyone else runs into that issue, make sure your script doesn't contain any relative path references as it won't work when run from VBA.

felix
  • 1