48

I am trying to call a Python script on VBA, and I am new to this.

I tried converting the main script to an EXE file using py2exe and then calling it from VBA (shell), but the main script calls other scripts, and therefore it becomes complicated and I messed it up (my EXE file is not functional). Besides, the the main script is a large file and I do not want to revise it a lot.

Bottom line, 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 it doesn't do anything else. Then I tried:

RetVal = Shell("C:\Windows\System32\cmd.exe " & "python " & "C:\\Python27\\hello.py")

It starts a command prompt, but it does not even start the Python interpreter.

P.S.: I checked all the related questions in the forum, they do not solve my problem.

Improve this question
2

13 Answers 13

Reset to default
40

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.

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

5 Comments

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.
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")
@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?
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??
How can I call a function with passing arguments using the Shell?
24

Nothing listed in the previous answers actually worked for me. I tested the script below, and it worked fine on my system.

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
Improve this answer

4 Comments

Use triple double quote """Path""" to handle the spaces in folder path is much better, Thanks for the script.
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.
Perfect! But how do we tell VBA to wait until hte Python program has finished its work?
The syntax highlighter does not like it. That can indicate it is not syntactically correct or the syntax highlighter is wrong. It is probably the latter, as the (current) syntax highlighter is also known as the weird syntax highlighter.
6

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.

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 writing 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 two (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.

Improve this answer

5 Comments

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.
I can recommend DataNitro. Excellent integration. Bit pricey though
To any others willing to explore paid add-ins, xlwings is another option which I've used recently to reasonable effect (available with a 30-day trial).
The first link still works (Google Code closed down in 2016). But consider migrating it, if possible.
The second link is broken (times out): "The connection has timed out. An error occurred during a connection to datanitro.com."
4

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.

Improve this answer

Comments

3

This code will work:

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

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

Improve this answer

2 Comments

Thank you for your answer. It would be improved however if you could provide a little explanation, not just code.
ActiveWorkbook.Path return the current directory of the workbook. The shell command open the file through the cmc of Windows.
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.

Improve this answer

Comments

0

ChDir "<Python script path>" was the solution for me. I use VBA from Microsoft Word to launch a Python 3 script.

Improve this answer

Comments

0

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

   ChDir "C:\Users\directory_where_the_script_is"
   Call Shell("C:\Users\...\python.exe " & "your_script_name.py", 1)
Improve this answer

Comments

0

For me this is not working:

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

For me this:

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

3 Comments

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review
Re "For me this:": Do you mean "For me this is working:"?
OK, the OP has left the building: "Last seen more than 1 year ago"
0

I think the previous answers have a catastrophic problem:

After running the macro, the cmd window would close automatically, instantly. In case the output is not as expected, you would have absolutely zero debug information.

Finally, I found a better way in which case, the cmd window remains open and shows all the information (print of Python) and error log.

The code is 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
Improve this answer

Comments

0

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

Improve this answer

Comments

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

Improve this answer

1 Comment

should say RetVal = Shell("python.exe <full path to python script>",vbNormalFocus) and replace <full path to python script> with the full path to your script.
-1

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. For example, 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 does appear, one of the first issues to check is the current working directory.

Improve this answer

1 Comment

The first part is plagiarised from Omi's answer. Is this a bogus answer?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.