10

I'm trying to run a Python script from my VBA module. I've tried pretty much every example I've seen on the Internet and so far didn't have any luck.

In the VBA module, I also run a .bat file, and it works perfectly:

batchname = "U:\Backup Bat File.bat"

Shell batchname, vbNormalFocus

Next, I need to run the Python script, which is located in the same folder as the Excel file.

Right now, I'm trying out this:

Dim Ret_Val

Dim args

args = Activeworkbook.Path & "\beps_output.py"

Ret_Val = Shell("C:\python34\python.exe" & args, vbNormalFocus)

It doesn't error out, but nothing happens. I'm a little confused at what the "Ret_Val" (return value?) does here, and why it isn't running.

Improve this question
2

4 Answers 4

Reset to default
7

This is running on my PC (Windows 7 x64 and Python 2.7.9):

Sub runpython()
    Dim Ret_Val
    Dim args As String

    args = "W:\programming\python\other_py\sqrt.py"
    Ret_Val = Shell("C:\Program Files (x86)\python27\python.exe" & " " & args, vbNormalFocus)
    If Ret_Val = 0 Then
       MsgBox "Couldn't run python script!", vbOKOnly
    End If
End Sub

Ret_Val will be nonzero if the call succeeds, namely the processID of the launched command. Note that the command will run asynchronously, i.e., the VBA code will terminate faster than the external command.

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

1 Comment

"Ret_Val will be non-zero if the call succeeds." Your code should therefore be: If Ret_Val = 0 Then
5

Try adding a space between the EXE file program and file:

Ret_Val = Shell("C:\python34\python.exe " & args, vbNormalFocus)

Also, because Shell is a function that returns a value (variant type - double) with specified arguments, the returned value can be contained in a variable, here as you specify with Ret_Val. You can then add conditional logic and error handling using this value.

Improve this answer

Comments

3

The difficult part for me was diagnosing what part of the code was not working. The cmd window flashes so fast you can't read the error messages. Just add this line of code above the Shell statement.

Call Shell("cmd.exe", vbNormalFocus)

Step through the code with F8.

When the cmd window pops up, type in the (fullpath)python.exe command and (fullpath)script file.py as you tried with your code. The cmd window now stays open, so you can see the messages.

In my case, it was a picture that needed a full path instead of assuming it was in the same directory.

Improve this answer

Comments

0

I had the same issue and solved it recently by doing the following:

One line of code worked for me.

Shell("python filepath of .py")

This code essentially opens cmd.exe and writes out the string within the "".

My .py file does need my workbook to be closed or else it won’t work. So I ended up running a separate VBA code to duplicate my current workbook and then my Python script runs on that duplicate, while my current active workbook executing the above VBA code was opened to run the VBA code.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.