How to run python coding in anaconda prompt using vba?-CodePudding

I am attempting to run python coding using vba. However, when running using vba, it was not successful . (i discovered that it is not running in anaconda prompt)

the code is attached as follow. appreciate the help.

Sub RunPythonScript()

Dim objShell As Object
Dim PythonExePath As String, PythonScriptPath As String

Set objShell = VBA.CreateObject("Wscript.Shell")
    
PythonExePath = """C:xxx.exe"""
PythonScriptPath = """C:xxx.py"""
    
objShell.Run PythonExePath & " " & PythonScriptPath
    
End Sub

Alternatively, I manually run in anaconda prompt and the code works.

"C:xxx.exe" "C:xxx.py"

CodePudding user response：

The code you provided looks like it is trying to run a Python script using the Wscript.Shell object in VBA, which is used to run external programs and scripts. However, this will not work for running a Python script in the Anaconda Prompt, as the Anaconda Prompt is a command-line interface (CLI) and not a script.

To run a Python script in the Anaconda Prompt using VBA, you will need to use the Shell function to run the python.exe executable in the Anaconda Prompt and pass your Python script as a command-line argument. Here is an example of how you could do this:

Sub RunPythonScript()
Dim pythonExePath As String, pythonScriptPath As String

' Replace "C:\Program Files\Anaconda3\python.exe" with the path to your Anaconda Python installation
pythonExePath = """C:\Program Files\Anaconda3\python.exe"""

' Replace "C:\scripts\myscript.py" with the path to your Python script
pythonScriptPath = """C:\scripts\myscript.py"""

Shell pythonExePath & " " & pythonScriptPath, vbNormalFocus
End Sub

This code will open the Anaconda Prompt and run the python.exe executable, passing the path to your Python script as a command-line argument. This will cause the Python script to be executed in the Anaconda Prompt.

edit; or you can try this

Sub RunPythonScript()
Dim objShell As Object
Dim PythonExePath As String, PythonScriptPath As String

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

PythonExePath = "C:xxx.exe"
PythonScriptPath = "C:xxx.py"

objShell.Exec PythonExePath & " " & PythonScriptPath
End Sub

CodePudding user response：

Thank you for the clarification! I attempted to apply first suggestion as follow.

Sub RunPythonScript()
Dim pythonExePath As String, pythonScriptPath As String

pythonExePath = """C:\Users\xxx\Anaconda3\python.exe"""
pythonScriptPath = """C:\Users\xxx\xxx.py"""

Shell pythonExePath & " " & pythonScriptPath, vbNormalFocus
End Sub

What I observed on screen was the black cmd window pop out and disappeared in second. It did not work as expected. Is there anything I input incorrectly?