How to run Python script in VBA by reading the path in a cell?

Question

I need to close a project, but most people cannot configure VBA to change paths (path made the change).

Sub RunPython_Click()

Dim objShell As Object
Dim PythonExe, PythonScript As String
    
    Set objShell = VBA.CreateObject("Wscript.Shell")
    PythonExe = Range("B13").Value
    PythonScript = Range("B14").Value
    
    objShell.Run PythonExe & " " & PythonScript
End Sub

I'm doing this so people don't have to go into the VBA configuration to make the script path changes.

Please do not post images of code: you can copy/paste the code directly into the post and format it using the {} button. What is the specific problem you're having with this task? In the event either of those paths might contain spaces, you should quote the paths in the call to Run — Tim Williams
– Tim Williams, Commented Mar 12, 2022 at 0:17
I need VBA to be able to read the path through the cell, i don't know if this code is correct. — DEV PYTHON
– DEV PYTHON, Commented Mar 12, 2022 at 0:36
Have you tried this? That would be a good first step. You should really specify a worksheet though: something like PythonExe = ThisWorkbook.Worksheets("Settings").Range("B13").Value — Tim Williams
– Tim Williams, Commented Mar 12, 2022 at 0:54
"it doesn't work" is not a very useful description of exactly what happens when you run your code. — Tim Williams
– Tim Williams, Commented Mar 12, 2022 at 0:58

Tim Williams · Accepted Answer · 2022-03-12 01:24:07Z

1

You can try this instead:

Sub RunPython_Click()
    Dim PythonExe, PythonScript As String
    
    'hard-coded for testing...
    PythonExe = "C:\Python\Python39-32\Python.exe"
    PythonScript = "C:\Python\Code\hello.py"
    
    ' /k keeps the window open so you can see any problems
    ' swap /k to /c when sure there's no errors
    Shell "cmd.exe /k """"" & PythonExe & """ """ & PythonScript & """""", vbNormalFocus
End Sub

answered Mar 12, 2022 at 1:24

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

8 Comments

DEV PYTHON Over a year ago

Yes, but what I need is to be able to collect the path from cell.

Tim Williams Over a year ago

Note I have "for testing" in the comment above. I assumed reading the paths from the cells wasn't the issue since you said the program "ran instantly". Anyway, try it with hard-coded paths and see what happens.

DEV PYTHON Over a year ago

Sorry, I understood it the wrong way. Yes, this way it runs right along with the python codes.

DEV PYTHON Over a year ago

But the error appeared: " C:\Python310\python.exe : can't open file" and " C:\\Project\\Script\\WorkScript.py," : [Error 2] No such file or directory.

Tim Williams Over a year ago

are there double backslashes in the path you supplied?

|

Collectives™ on Stack Overflow

How to run Python script in VBA by reading the path in a cell?

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related