ExcelBanter - Shell Statement

"Confused@424" wrote in message
om...
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
http://www.appspro.com/

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

Option Explicit

Private Const PROCESS_QUERY_INFORMATION As Long = &H400
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
must
''' 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
pass
''' 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.
Do
''' lExitCode will be set to STILL_ACTIVE as long as the shelled
process is running.
lResult = GetExitCodeProcess(lProcess, lExitCode)
DoEvents
Loop While lExitCode = STILL_ACTIVE

End Sub