Thread: Shell Statement
View Single Post
  #2   Report Post  
Old August 13th 03, 08:19 PM posted to microsoft.public.excel.programming
Rob Bovey Rob Bovey is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2003
Posts: 811
Default Shell Statement

"[email protected]" wrote in message
I initiated a shell statement to kick off an external bat
process...however I can't figure out how to have excel wait until the
process is done before importing the data.

Pasted below is some code demonstrating how to have your VBA code wait
for a shelled process.

Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Option Explicit

Private Const STILL_ACTIVE As Long = &H103

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

Sub TestShellAndWait()
ShellAndWait "Calc.exe", vbNormalFocus
MsgBox "Returned from calculator"
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
''' Comments: Shells out to the specified command line and waits for it to
''' complete. The Shell function runs asynchronously, so you
''' run it using this function if you need to do something with
''' its output or wait for it to finish before continuing.
''' Arguments: szCommandLine The command line to execute using Shell.
''' iWindowState (Optional) The window state parameter to
''' to the Shell function. Default = vbHide.
''' See Shell function help for other options.
''' Date Developer Action
''' ------------------------------------------------------------------------
''' 01/14/99 Rob Bovey Created
Sub ShellAndWait(ByVal szCommandLine As String, Optional ByVal iWindowState
As Integer = vbHide)

Dim lTaskID As Long
Dim lProcess As Long
Dim lExitCode As Long
Dim lResult As Long

''' Run the Shell function.
lTaskID = Shell(szCommandLine, iWindowState)

''' Check for errors.
If lTaskID = 0 Then Err.Raise Number:=vbObjectError + 1,
Description:="Shell function error."

''' Get the process handle from the task ID returned by Shell.
lProcess = OpenProcess(PROCESS_QUERY_INFORMATION, 0&, lTaskID)

''' Check for errors.
If lProcess = 0 Then Err.Raise Number:=vbObjectError + 1,
Description:="Unable to open Shell process handle."

''' Loop while the shelled process is still running.
''' lExitCode will be set to STILL_ACTIVE as long as the shelled
process is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
Loop While lExitCode = STILL_ACTIVE

End Sub