等待Shell完成，然后格式化单元格——同步执行一条命令 [英] Wait for Shell to finish, then format cells - synchronously execute a command

问题描述

我有一个使用 shell 命令调用的可执行文件:

I have an executable that I call using the shell command:

Shell (ThisWorkbook.Path & "\ProcessData.exe")

可执行文件进行一些计算，然后将结果导出回 Excel.我希望能够在导出结果后更改结果的格式.

The executable does some computations, then exports results back to Excel. I want to be able to change the format of the results AFTER they are exported.

换句话说，我首先需要 Shell 命令等待，直到可执行文件完成其任务，导出数据，然后执行下一个命令进行格式化.

In other words, i need the Shell command first to WAIT until the executable finishes its task, exports the data, and THEN do the next commands to format.

我尝试了 Shellandwait()，但运气不佳.

I tried the Shellandwait(), but without much luck.

我有:

Sub Test()

ShellandWait (ThisWorkbook.Path & "\ProcessData.exe")

'Additional lines to format cells as needed

End Sub

不幸的是，在可执行文件完成之前，仍然先进行格式化.

Unfortunately, still, formatting takes place first before the executable finishes.

仅供参考，这是我使用 ShellandWait 的完整代码

Just for reference, here was my full code using ShellandWait

' Start the indicated program and wait for it
' to finish, hiding while we wait.


Private Declare Function CloseHandle Lib "kernel32.dll" (ByVal hObject As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32.dll" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function OpenProcess Lib "kernel32.dll" (ByVal dwDesiredAccessas As Long, ByVal bInheritHandle As Long, ByVal dwProcId As Long) As Long
Private Const INFINITE = &HFFFF


Private Sub ShellAndWait(ByVal program_name As String)
Dim process_id As Long
Dim process_handle As Long

' Start the program.
On Error GoTo ShellError
process_id = Shell(program_name)
On Error GoTo 0

' Wait for the program to finish.
' Get the process handle.
process_handle = OpenProcess(SYNCHRONIZE, 0, process_id)
If process_handle <> 0 Then
WaitForSingleObject process_handle, INFINITE
CloseHandle process_handle
End If

Exit Sub

ShellError:
MsgBox "Error starting task " & _
txtProgram.Text & vbCrLf & _
Err.Description, vbOKOnly Or vbExclamation, _
"Error"

End Sub

Sub ProcessData()

  ShellAndWait (ThisWorkbook.Path & "\Datacleanup.exe")

  Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
End Sub

推荐答案

尝试 WshShell 对象 而不是原生的 Shell 函数.

Try the WshShell object instead of the native Shell function.

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim errorCode As Long

errorCode = wsh.Run("notepad.exe", windowStyle, waitOnReturn)

If errorCode = 0 Then
    MsgBox "Done! No error to report."
Else
    MsgBox "Program exited with error code " & errorCode & "."
End If    

但请注意:

如果bWaitOnReturn设置为false(默认)，Run方法在程序启动后立即返回，自动返回0(不会被解释为错误代码).

If bWaitOnReturn is set to false (the default), the Run method returns immediately after starting the program, automatically returning 0 (not to be interpreted as an error code).

因此要检测程序是否成功执行，您需要将 waitOnReturn 设置为 True，如我上面的示例所示.否则无论如何它都会返回零.

So to detect whether the program executed successfully, you need waitOnReturn to be set to True as in my example above. Otherwise it will just return zero no matter what.

对于早期绑定(允许访问自动完成)，设置对Windows 脚本宿主对象模型"的引用(工具 > 参考 > 设置复选标记)并声明如下:

For early binding (gives access to Autocompletion), set a reference to "Windows Script Host Object Model" (Tools > Reference > set checkmark) and declare like this:

Dim wsh As WshShell 
Set wsh = New WshShell

现在运行你的进程而不是记事本......我希望你的系统会拒绝包含空格字符的路径 (...\My Documents\..., ...\Program Files\... 等)，因此您应该将路径括在 "quotes" 中:

Now to run your process instead of Notepad... I expect your system will balk at paths containing space characters (...\My Documents\..., ...\Program Files\..., etc.), so you should enclose the path in "quotes":

Dim pth as String
pth = """" & ThisWorkbook.Path & "\ProcessData.exe" & """"
errorCode = wsh.Run(pth , windowStyle, waitOnReturn)

这篇关于等待Shell完成，然后格式化单元格——同步执行一条命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！