Excel宏中实现PowerShell日志实时监控并自动关闭窗口

问题根源 #

你之前尝试用objShell.Run Command, 1获取PID失败，是因为WScript.Shell.Run的返回值不是进程ID，而是进程执行完成后的退出状态码。只有当设置第三个参数为True（同步执行，VBA会等待进程结束才继续）时才会返回该状态码，但这里PowerShell因get-content -wait持续运行，同步执行会直接卡住VBA，所以这种方式不可行。

方案一：通过WshExec获取PID并强制终止进程 #

改用WScript.Shell.Exec方法，它会返回WshExec对象，包含进程的ProcessID和Terminate方法，可直接在宏结束时终止PowerShell进程。

修改后的完整VBA代码：

Sub MyTest()
    Dim LogFile As String, Command As String
    Dim fsoScript As Object, Fileout As Object
    Dim objShell As Object, psProcess As Object

    LogFile = "C:\Temp\test.txt"
    ' 构造PowerShell命令，注意转义双引号
    Command = "PowerShell -command ""get-content '" & LogFile & "' -Tail 0 -wait"""

    ' 创建日志文件
    Set fsoScript = CreateObject("Scripting.FileSystemObject")
    Set Fileout = fsoScript.CreateTextFile(LogFile, True, True)
    Fileout.Write ""
    Fileout.Close

    ' 用Exec启动PowerShell，获取进程对象
    Set objShell = CreateObject("WScript.Shell")
    Set psProcess = objShell.Exec(Command)

    ' 更新日志内容
    Application.Wait (Now + TimeValue("0:00:02"))
    Set Fileout = fsoScript.OpenTextFile(LogFile, 8, True, -2)
    Fileout.Write Now() & " - test1" & vbCrLf
    Fileout.Close

    Application.Wait (Now + TimeValue("0:00:02"))
    Set Fileout = fsoScript.OpenTextFile(LogFile, 8, True, -2)
    Fileout.Write Now() & " - test2" & vbCrLf
    Fileout.Close

    Application.Wait (Now + TimeValue("0:00:02"))
    Set Fileout = fsoScript.OpenTextFile(LogFile, 8, True, -2)
    Fileout.Write Now() & " - test3" & vbCrLf
    Fileout.Close

    MsgBox "Done"

    ' 终止PowerShell进程
    If Not psProcess Is Nothing Then
        On Error Resume Next
        psProcess.Terminate
        On Error GoTo 0
    End If

    ' 清理对象
    Set psProcess = Nothing
    Set objShell = Nothing
    Set Fileout = Nothing
    Set fsoScript = Nothing
End Sub

方案二：让PowerShell自动退出（无需强制杀进程） #

修改PowerShell命令，让它同时监控日志文件和一个"退出标记文件"，宏结束时创建标记文件，PowerShell检测到后自动退出。

修改后的VBA代码：

Sub MyTest()
    Dim LogFile As String, ExitFlagFile As String, Command As String
    Dim fsoScript As Object, Fileout As Object
    Dim objShell As Object

    LogFile = "C:\Temp\test.txt"
    ExitFlagFile = "C:\Temp\exit.tmp"

    ' 构造带退出检测的PowerShell命令
    Command = "PowerShell -command ""$logFile='" & LogFile & "';$exitFlag='" & ExitFlagFile & "';" & _
              "if(Test-Path $exitFlag){Remove-Item $exitFlag};" & _
              "Get-Content $logFile -Tail 0 -Wait | ForEach-Object {$_;if(Test-Path $exitFlag){exit}}"""

    ' 初始化文件（删除旧标记、创建空日志）
    Set fsoScript = CreateObject("Scripting.FileSystemObject")
    If fsoScript.FileExists(ExitFlagFile) Then fsoScript.DeleteFile ExitFlagFile
    Set Fileout = fsoScript.CreateTextFile(LogFile, True, True)
    Fileout.Write ""
    Fileout.Close

    ' 启动PowerShell监控
    Set objShell = CreateObject("WScript.Shell")
    objShell.Run Command, 1

    ' 更新日志内容
    Application.Wait (Now + TimeValue("0:00:02"))
    Set Fileout = fsoScript.OpenTextFile(LogFile, 8, True, -2)
    Fileout.Write Now() & " - test1" & vbCrLf
    Fileout.Close

    Application.Wait (Now + TimeValue("0:00:02"))
    Set Fileout = fsoScript.OpenTextFile(LogFile, 8, True, -2)
    Fileout.Write Now() & " - test2" & vbCrLf
    Fileout.Close

    Application.Wait (Now + TimeValue("0:00:02"))
    Set Fileout = fsoScript.OpenTextFile(LogFile, 8, True, -2)
    Fileout.Write Now() & " - test3" & vbCrLf
    Fileout.Close

    MsgBox "Done"

    ' 创建退出标记，触发PowerShell自动退出
    Set Fileout = fsoScript.CreateTextFile(ExitFlagFile, True, True)
    Fileout.Write ""
    Fileout.Close
    ' 给PowerShell留处理时间
    Application.Wait (Now + TimeValue("0:00:01"))
    ' 删除标记文件
    fsoScript.DeleteFile ExitFlagFile

    ' 清理对象
    Set objShell = Nothing
    Set Fileout = Nothing
    Set fsoScript = Nothing
End Sub

内容的提问来源于stack exchange，提问作者some.help.please