从VBA调用Python脚本-不起作用

Question

我参考了这里给出的答案-如何在excel vba上调用python脚本但这对我不起作用。

我只是看到屏幕在我面前闪烁，Python脚本根本无法运行我正在编写的Python脚本是在Spyder中，并且有多个模块引用它。

这是我正在写的代码：

Sub RunPythonScript()
Dim objShell As Object
Dim PythonExePath As String, PythonScriptPath As String
ActiveWorkbook.Save
ChDir "C:pathwhere.pyfileissaved"
Set objShell = VBA.CreateObject("Wscript.Shell")

PythonExePath = """C:pathwherepython.exeexists"""
PythonScriptPath = """C:pathwhere.pyfileissaved"""

objShell.Run PythonExePath & PythonScriptPath
Application.Goto Reference:="RunPythonScript"

objShell.Popup "Your results are now processed", , "Attention!"

End Sub

Answer 1

这样尝试：

Sub RunPythonScript()
Dim exe, pth

exe = "C:pathwherepython.exeexists"
pth = "C:pathwhere.pyfileissaved"

Shell "cmd.exe /k """"" & exe & """ """ & pth & """""", vbNormalFocus
MsgBox "Your results are now processed", vbInformation, "Attention!"
End Sub

https://www.myonlinetraininghub.com/vba-shell

我不知道你的Application.Goto是干什么的。

Answer 2

使用shell命令运行类似python的极端脚本&以字符串形式获取输出。我使用vba中的python日期时间方法将其用于日期时间类型的数据。

Public Function ShellRun(sCmd As String) As String
'sCmd = "python.exe test.py arg" as example
'Run a python in shell command & returning the output as a string

Dim osh As Object
Dim oEx As Object
Dim oOp As Object
Dim s As String
Dim sLine As String

Set osh = CreateObject("WScript.Shell")
Set oEx = osh.Exec(sCmd)
Set oOp = oEx.Stdout
'reed output line from oOp (StdOut object)
While Not oOp.atEndOfStream
sLine = oOp.ReadLine
If sLine <> "" Then s = s & sLine & vbCrLf
Wend
ShellRun = s
End Function