Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 811
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Running Shell Jeff Excel Discussion (Misc queries) 0 January 28th 08 05:50 PM
Is it possible to Shell and remove Excel from memory? alainB Excel Discussion (Misc queries) 3 August 2nd 06 04:16 PM
maximum number of letters which can be entered in excell shell Helena Excel Worksheet Functions 1 March 13th 06 11:17 PM
Loading PDFs, Shell Function Paul Martin Excel Programming 5 July 31st 03 02:52 AM
use shell function in vbscript Sofia Excel Programming 0 July 21st 03 06:29 PM


All times are GMT +1. The time now is 02:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"