ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Shell Statement (https://www.excelbanter.com/excel-programming/274341-shell-statement.html)

[email protected] August 13th 03 08:09 PM

Shell Statement
 
Hi, Can anyone please help.

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.

I can use a splash statement or a wait statement but would rather find
a better way to hold off until my data is available.

thanks for any help you may offer.

Rob Bovey August 13th 03 08:19 PM

Shell Statement
 
"[email protected]" 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





All times are GMT +1. The time now is 12:01 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com