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

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.

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.

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.

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 & PythonScript

End Sub

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

Try this:

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

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

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.

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.

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