通过Excel/VBA运行Python脚本

Question

我已经试图寻找这个问题的解决方案很长时间了。我有一些需要定期更新的Python脚本，希望能够使用宏来完成这个任务。现在，我双击文件，它们通过Shell执行没有问题。

Sub RunPyScript()

Dim Ret_Val As Variant
Dim command As String

command = Chr(34) & "C:\Users\Jon Doe\python.exe" & Chr(34) & " " & Chr(34) & "C:\Users\Jon Doe\" & "\Roto.py" & Chr(34)
Ret_Val = Shell(command, vbNormalFocus)

End Sub

当我尝试运行上述宏时，看起来它将像双击时运行相同，但是Shell在脚本执行之前退出（我认为这是问题所在，不确定）。如果有人能帮助我，我会非常感激。

- Nick

Answer 1

Dim objShell
'Dim command             'use this declaration type for VBS (Script), instead of the direct declaration like as "As String"
Dim command as String   'use this declaration type for VBA (Access/Excel)

command = Chr(34) & "C:\Users\John Doe\python.exe" & Chr(34) & " " & Chr(34) & "C:\Users\John Doe\" & "\roto.py" & Chr(34)
Set objShell = CreateObject("WScript.Shell")

objShell.Run command, 1, True

'Settings for WindowStyle:
'     0 Hide the window (and activate another window.)
'     1 Activate and display the window. (restore size and position) Specify this flag when displaying a window for the first time.
'     2 Activate & minimize.
'     3 Activate & maximize.
'     4 Restore. The active window remains active.
'     5 Activate & Restore.
'     6 Minimize & activate the next top-level window in the Z order.
'     7 Minimize. The active window remains active.
'     8 Display the window in its current state. The active window remains active.
'     9 Restore & Activate. Specify this flag when restoring a minimized window.
'    10 Sets the show-state based on the state of the program that started the application.

顺便提一下，您可以简单地使用双引号来将引号插入字符串变量中，就像我在之前的源代码中使用的那样。例如，StrVariable="""" 将得到结果 "。因此，命令行字符串将为:

command = """%USERPROFILE%\python.exe"" ""%USERPROFILE%\roto.py"""

其中结果为:

"C:\Users\John Doe\python.exe" "C:\Users\John Doe\roto.py"

- s3n0