python - 通过 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 在脚本执行前退出(我认为这是问题所在，不是积极的)。如果有人可以帮助我，我将不胜感激。

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.

顺便说一句，您可以简单地使用 2x 引号将引号插入字符串变量，就像我在以前的源代码中使用的那样。例如 StrVariable="""" 给出结果 "。那么命令行的字符串将是:

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

结果是:

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