VBA Shell函数无法执行Python scrip

2024-03-29 13:59:34 发布

您现在位置:Python中文网/ 问答频道 /正文

我试图使用下面的VBA代码执行Python脚本(请运行sub Test()

Option Explicit

#If VBA7 And Win64 Then
    Declare PtrSafe Function OpenProcess Lib "kernel32" _
    (ByVal dwDesiredAccess As Long, _
     ByVal bInheritHandle As Long, _
     ByVal dwProcessId As Long) As Long

    Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" _
    (ByVal hProcess As Long, lpExitCode As Long) As Long
#Else
    Declare Function OpenProcess Lib "kernel32" _
    (ByVal dwDesiredAccess As Long, _
     ByVal bInheritHandle As Long, _
     ByVal dwProcessId As Long) As Long

    Declare Function GetExitCodeProcess Lib "kernel32" _
    (ByVal hProcess As Long, lpExitCode As Long) As Long
#End If

Const interpreter_path As String = "C:\Users\dongm\Anaconda2\python.exe"


Sub Test()
    RegressAndWait "Output"
End Sub

Private Sub RegressAndWait(ByVal output_folder_name As String)

    Dim TaskID As Long, hProc As Long, lExitCode As Long
    Dim ACCESS_TYPE As Integer, STILL_ACTIVE As Integer

    ACCESS_TYPE = &H400
    STILL_ACTIVE = &H103

    Dim Program As String, args(1 To 5) As String
    args(1) = interpreter_path
    args(2) = ThisWorkbook.path & "\regressions.py"
    args(3) = """" & "dummy" & """"
    args(4) = """" & "." & """"
    args(5) = """" & output_folder_name & """"
    Program = Join(args, " ")

    ChDir ThisWorkbook.path
    TaskID = Shell(Program, vbNormalFocus)
    hProc = OpenProcess(ACCESS_TYPE, False, TaskID)
    If Err <> 0 Then
        MsgBox "Cannot run regressions.py", vbCritical, "Error"
        Exit Sub
    End If
    Do
        GetExitCodeProcess hProc, lExitCode
        DoEvents
    Loop While lExitCode = STILL_ACTIVE
End Sub

运行sub Test()后,我可以看到提示窗口闪烁,然后退出,就好像打开了提示窗口但从未使用过一样(我看不到是否有任何消息写入提示窗口,因为它很快就消失了)。在

但是,我可以不使用VBA手动运行python脚本,方法是打开Anaconda Prompt窗口,然后打开cd到我当前的文件夹地址(为什么是ChDir ThisWorkbook.path),然后在提示窗口中输入python regressions.py "dummy" "."来运行python脚本。在

我的VBA代码基本上只是重复我手动完成的工作,但为什么它不起作用?在

另外,奇怪的是,我的代码以前运行得很好,但是今天早上我发现它不再工作了。在


仅供参考,在跳转到Shell函数之前,我使用调试器进入了Program,立即窗口中的?Program为我提供了

C:\Users\dongm\Anaconda2\python.exe C:\Company\Insurance Project\Health Insurance Company Regression\regressions.py "dummy" "." "Output"


经过一些操作之后,我不能再调用Python脚本的原因似乎是因为我的文件夹名称中有空格。我记得,今天早上我把相关文件移到了另一个目录。我刚把Insurance Project改成{},把Health Insurance Company Regression改成{},然后一切恢复正常。这真是一个有趣的错误,我以前从未见过,但我想知道是什么原因造成的。Excel VBA还是Python和Anaconda?在


Tags: path脚本iflibasargsfunctionvba