我在我的 Access DB 中使用 VBA 來啟動一個鏈接到 PS1 腳本的批處理檔案。
一切都按預期作業。問題是我想在該操作完成后運行一些查詢，但就目前而言，我需要照看整個事情。所以我正在尋找一種解決方案，以在批處理運行時保持 VBA 暫停。
我找到了這篇文章：https ://danwagner.co/how-to-run-a-batch-file-and-wait-until-it-finishes-with-vba/
但由于某種原因，該解決方案對我不起作用。批處理運行，但 VBA 只是繼續前進而沒有暫停。
這是我的代碼：
Private Sub Button_UpdateOffline_Click()
Dim strCommand As String
Dim lngErrorCode As Long
Dim wsh As WshShell
Set wsh = New WshShell
DoCmd.OpenForm "Please_Wait"
'Run the batch file using the WshShell object
strCommand = Chr(34) & _
"C:\Users\Rip\Q_Update.bat" & _
Chr(34)
lngErrorCode = wsh.Run(strCommand, _
WindowStyle:=0, _
WaitOnReturn:=True)
If lngErrorCode <> 0 Then
MsgBox "Uh oh! Something went wrong with the batch file!"
Exit Sub
End If
DoCmd.Close acForm, "Please_Wait"
End Sub
如果有幫助，這是我的批處理代碼：
START PowerShell.exe -ExecutionPolicy Bypass -Command "& 'C:\Users\Rip\PS1\OfflineFAQ_Update.ps1' "
uj5u.com熱心網友回復：
您的批處理代碼啟動 PowerShell，然后關閉。
VBA 會等到您的批處理代碼啟動 PowerShell，然后繼續。它無法知道您是否真的想等到PowerShell完成，因為如果您想要等待，您也必須讓您的批處理腳本等待。
/WAIT因此，除了評論中建議的更改之外，要么將批處理代碼更改為 include ：
START /wait PowerShell.exe -ExecutionPolicy Bypass -Command "& 'C:\Users\Rip\PS1\OfflineFAQ_Update.ps1' "
或者，直接打開 PowerShell，中間沒有批處理檔案：
strCommand = "PowerShell.exe -ExecutionPolicy Bypass -Command ""& 'C:\Users\Rip\PS1\OfflineFAQ_Update.ps1' """
uj5u.com熱心網友回復：
使用WaitForSingleObject API 呼叫。它需要一些冗長的代碼，但是當包裝在如下函式中時實作起來非常簡單ShellWait。
這樣，您就可以直接呼叫 PowerShell 命令，如本例所示：
' Unblock a file or all files of a folder.
'
' 2022-10-18. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function UnblockFiles( _
ByVal Path As String) _
As Boolean
Const CommandMask As String = "PowerShell -command {0}"
Const ArgumentMask As String = "Dir ""{0}"" -Recurse | Unblock-File"
Const WindowStyle As Long = VbAppWinStyle.vbHide
Dim Argument As String
Dim Command As String
Dim Result As Long
Dim Success As Boolean
If Dir(Path, vbDirectory) = "" Then
' Path is neither a file nor a folder.
Else
' Continue.
Argument = Replace(ArgumentMask, "{0}", Path)
Command = Replace(CommandMask, "{0}", Argument)
Result = ShellWait(Command, WindowStyle)
Success = Not CBool(Result)
End If
UnblockFiles = Success
End Function
你會看到，它command是由前兩個常量和一個變陣列裝而成的。您應該能夠為您的命令修改那些。
我ShellWait使用：
' General constants.
'
' Wait forever.
Private Const Infinite As Long = &HFFFF
' Process Security and Access Rights.
'
' The right to use the object for synchronization.
' This enables a thread to wait until the object is in the signaled state.
Private Const Synchronize As Long = &H100000
' Constants for WaitForSingleObject.
'
' The specified object is a mutex object that was not released by the thread
' that owned the mutex object before the owning thread terminated.
' Ownership of the mutex object is granted to the calling thread and the
' mutex state is set to nonsignaled.
Private Const StatusAbandonedWait0 As Long = &H80
Private Const WaitAbandoned As Long = StatusAbandonedWait0 0
' The state of the specified object is signaled.
Private Const StatusWait0 As Long = &H0
Private Const WaitObject0 As Long = StatusWait0 0
' The time-out interval elapsed, and the object's state is nonsignaled.
Private Const WaitTimeout As Long = &H102
' The function has failed. To get extended error information, call GetLastError.
Private Const WaitFailed As Long = &HFFFFFFFF
' Missing enum when using late binding.
'
#If EarlyBinding = False Then
Public Enum IOMode
ForAppending = 8
ForReading = 1
ForWriting = 2
End Enum
#End If
' API declarations.
' Opens an existing local process object.
' If the function succeeds, the return value is an open handle
' to the specified process.
' If the function fails, the return value is NULL (0).
' To get extended error information, call GetLastError.
'
#If VBA7 Then
Private Declare PtrSafe Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) _
As LongPtr
#Else
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) _
As Long
#End If
' The WaitForSingleObject function returns when one of the following occurs:
' - the specified object is in the signaled state.
' - the time-out interval elapses.
'
' The dwMilliseconds parameter specifies the time-out interval, in milliseconds.
' The function returns if the interval elapses, even if the object's state is
' nonsignaled.
' If dwMilliseconds is zero, the function tests the object's state and returns
' immediately.
' If dwMilliseconds is Infinite, the function's time-out interval never elapses.
'
#If VBA7 Then
Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" ( _
ByVal hHandle As LongPtr, _
ByVal dwMilliseconds As Long) _
As Long
#Else
Private Declare Function WaitForSingleObject Lib "kernel32" ( _
ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) _
As Long
#End If
' Closes an open object handle.
' If the function succeeds, the return value is nonzero.
' If the function fails, the return value is zero.
' To get extended error information, call GetLastError.
'
#If VBA7 Then
Private Declare PtrSafe Function CloseHandle Lib "kernel32" ( _
ByVal hObject As LongPtr) _
As Long
#Else
Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) _
As Long
#End If
' Shells out to an external process and waits until the process ends.
' Returns 0 (zero) for no errors, or an error code.
'
' The call will wait for an infinite amount of time for the process to end.
' The process will seem frozen until the shelled process terminates. Thus,
' if the shelled process hangs, so will this.
'
' A better approach could be to wait a specific amount of time and, when the
' time-out interval expires, test the return value. If it is WaitTimeout, the
' process is still not signaled. Then either wait again or continue with the
' processing.
'
' Waiting for a DOS application is different, as the DOS window doesn't close
' when the application is done.
' To avoid this, prefix the application command called (shelled to) with:
' "command.com /c " or "cmd.exe /c ".
'
' For example:
' Command = "cmd.exe /c " & Command
' Result = ShellWait(Command)
'
' 2018-04-06. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function ShellWait( _
ByVal Command As String, _
Optional ByVal WindowStyle As VbAppWinStyle = vbNormalNoFocus) _
As Long
Const InheritHandle As Long = &H0
Const NoProcess As Long = 0
Const NoHandle As Long = 0
#If VBA7 Then
Dim ProcessHandle As LongPtr
#Else
Dim ProcessHandle As Long
#End If
Dim DesiredAccess As Long
Dim ProcessId As Long
Dim WaitTime As Long
Dim Closed As Boolean
Dim Result As Long
If Len(Trim(Command)) = 0 Then
' Nothing to do. Exit.
Else
ProcessId = Shell(Command, WindowStyle)
If ProcessId = NoProcess Then
' Process could not be started.
Else
' Get a handle to the shelled process.
DesiredAccess = Synchronize
ProcessHandle = OpenProcess(DesiredAccess, InheritHandle, ProcessId)
' Wait "forever".
WaitTime = Infinite
' If successful, wait for the application to end and close the handle.
If ProcessHandle = NoHandle Then
' Should not happen.
Else
' Process is running.
Result = WaitForSingleObject(ProcessHandle, WaitTime)
' Process ended.
Select Case Result
Case WaitObject0
' Success.
Case WaitAbandoned, WaitTimeout, WaitFailed
' Know error.
Case Else
' Other error.
End Select
' Close process.
Closed = CBool(CloseHandle(ProcessHandle))
If Result = WaitObject0 Then
' Return error if not closed.
Result = Not Closed
End If
End If
End If
End If
ShellWait = Result
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/535470.html