Running a Python from Excel Macro

Question

I have seen a couple posts instructing people on how to run a Python script through an Excel macro. I'm still having trouble with it - all I'm looking to do for the moment is see how I can get a Python script titled main.py to print 40 when I run an Excel macro. I've posted a couple filepaths below, with ellipses to replace the names of certain folders.

For background, my python.exe file is located:

R:\TRADING\...\Anders\python3.9 - Copy\3.9.7\python.exe

And the python script that I want to run is located:

R:\TRADING\...\Anders\Python\main.py

When I run print(sys.executable) on Python, it produces the location //Naeast.../.../.../.../TRADING/.../Anders/Python/main.py where //Naeast.../.../.../.../ is the location of the R: drive.

Per the response (with about 16 upvotes) to this post here, I implemented and ran this code below from my test Excel file to run main.py but it failed:

Sub RunPython1()

Dim objShell As Object
Dim PythonExe, PythonScript As String

    Set objShell = VBA.CreateObject("Wscript.Shell")

    PythonExe = """R:\TRADING\...\Anders\python3.9 - Copy\3.9.7\python.exe"""
    PythonScript = "R:\TRADING\...\Anders\Python\main.py"

    objShell.Run PythonExe & PythonScript

End Sub

I was careful to include triple quotes for PythonExe.

When I try replicating the answer to the question here, I get an error saying that the Method 'Run' of object 'IwshShell3' failed.

I think you need a space between the arguments `PythonExe & " " & PythonScript` — CDP1802, Jan 20 '22 at 19:40
@CDP1802, thank you - I tried adding the space " " in between PythonExe and PythonScript as well as adjusting the naming/path conventions for PythonScript, but this still did not end up helping my Python script run when I clicked the macro. — Anders Zhou, Jan 20 '22 at 20:09
Are there any spaces in folder names in `R:\TRADING\...\Anders\Python\main.py` — CDP1802, Jan 20 '22 at 20:30
No, there are no spaces in ```R:\TRADING\...\Anders\Python\main.py``` — Anders Zhou, Jan 20 '22 at 20:51

score 1 · Answer 1 · answered Jan 20 '22 at 21:17

1

Try running Python with this test script.

Sub test()

   Const PyExe = """R:\TRADING\...\Anders\python3.9 - Copy\3.9.7\python.exe"""
   Const PyScript = "-h"
   
   Dim objShell As Object, cmd As String
   Set objShell = CreateObject("Wscript.Shell")
   
   cmd = PyExe & " " & PyScript
   Debug.Print cmd
   
   MsgBox objShell.exec(cmd).StdOut.ReadAll
   
End Sub

answered Jan 20 '22 at 21:17

CDP1802

13,871
2
7
17

Hello, thank you! I ran this and got a message box that said "Microsoft Excel ... usage: \\Naeast... ... [-c cmd | -m mod | file | -] [arg] ... Options and arguments (and corresponding environment variables" ... and then it lists a bunch of options and arguments like "-b, -B, -c, -d, -E, -h, -i, and -I" which it then explains what each does. – Anders Zhou Jan 21 '22 at 16:20
1

@Anders That's good, it should be the Python help page. Put your script file in `PyScript` to replace -h – CDP1802 Jan 21 '22 at 16:36
Hey there! 'pologies for taking nearly two weeks to get back to you, but I'll comment back below - the solution was basically yours. – Anders Zhou Feb 08 '22 at 17:45

score 0 · Accepted Answer · answered Feb 08 '22 at 17:48

My python code was originally geared to edit an Excel spreadsheet directly. The use case in general was that whenever I received an email with a certain subject line - let's say blah for example, I wanted a python file main.py to automatically execute and edit an excel file main.xlsx accordingly.

Something to note is that main.xlsx cannot be open at the time the email arrives. If anyone has any guidance on how to have main.xlsx be modified on the chance that the email arrives while it is open, I'd greatly appreciate it.

The below code solved my issue exactly:

Private Sub inboxItems_ItemAdd(ByVal Item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
Dim MessageInfo
Dim Result
If TypeName(Item) = "MailItem" Then
    Debug.Print "Arrived"
    If Item.Subject = "Blah" Then
        Const PyExe = """C:\...\...\...\...\...\python3.9\latest\python.exe"""
        Const PyScript = "R:\...\...\Anders\Python\main.py"
        
        Dim objShell As Object, cmd As String
        Set objShell = CreateObject("Wscript.Shell")
        
        cmd = PyExe & " " & PyScript
        Debug.Print cmd
        
        objShell.Run cmd
        objShell.exec cmd
        
        MsgBox objShell.exec(cmd).StdOut.ReadAll
    End If
End If
ExitNewItem:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitNewItem
End Sub

Running a Python from Excel Macro

2 Answers2