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
– CDP1802, Commented Jan 20, 2022 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
– Anders Zhou, Commented Jan 20, 2022 at 20:09
Are there any spaces in folder names in R:\TRADING\...\Anders\Python\main.py — CDP1802
– CDP1802, Commented Jan 20, 2022 at 20:30
No, there are no spaces in R:\TRADING\...\Anders\Python\main.py — Anders Zhou
– Anders Zhou, Commented Jan 20, 2022 at 20:51

CDP1802 · Accepted Answer · 2022-01-20 21:17:25Z

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, 2022 at 21:17

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

3 Comments

Anders Zhou Over a year ago

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.

CDP1802 Over a year ago

@Anders That's good, it should be the Python help page. Put your script file in PyScript to replace -h

Anders Zhou Over a year ago

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 · Accepted Answer · 2022-02-08 17:48:42Z

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

Collectives™ on Stack Overflow

Running a Python from Excel Macro

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related