logo ASAP Utilities

Excel tip: Start an application from Excel and wait untill it is finished

Date: 5 september 2001

It is possible to lauch external programs from Excel by using the Shell() command in VBA. Sometimes you want your code to pause, and continue when the launched program has finished.
The msiShellAndWait() function will do that for you. Credits go to the original author (which name I've lost).


Copy-paste friendly code:
Option Explicit

'Windows API/Global Declarations for :Shell And Wait
Private Const INFINITE = -1&
Private Const NORMAL_PRIORITY_CLASS = &H20&
Public Const STARTF_FORCEOFFFEEDBACK = &H80
Public Const STARTF_FORCEONFEEDBACK = &H40
Public Const STARTF_RUNFULLSCREEN = &H20            ' ignored For non-x86 platforms
Public Const STARTF_USECOUNTCHARS = &H8
Public Const STARTF_USEFILLATTRIBUTE = &H10
Public Const STARTF_USEPOSITION = &H4
Public Const STARTF_USESHOWWINDOW = &H1
Public Const STARTF_USESIZE = &H2
Public Const STARTF_USESTDHANDLES = &H100
Public Const SW_ERASE = &H4
Public Const SW_HIDE = 0
Public Const SW_INVALIDATE = &H2
Public Const SW_MAX = 10
Public Const SW_MAXIMIZE = 3
Public Const SW_MINIMIZE = 6
Public Const SW_NORMAL = 1
Public Const SW_OTHERUNZOOM = 4
Public Const SW_OTHERZOOM = 2
Public Const SW_PARENTCLOSING = 1
Public Const SW_PARENTOPENING = 3
Public Const SW_RESTORE = 9
Public Const SW_SCROLLCHILDREN = &H1
Public Const SW_SHOW = 5
Public Const SW_SHOWDEFAULT = 10
Public Const SW_SHOWMAXIMIZED = 3
Public Const SW_SHOWMINIMIZED = 2
Public Const SW_SHOWMINNOACTIVE = 7
Public Const SW_SHOWNA = 8
Public Const SW_SHOWNOACTIVATE = 4
Public Const SW_SHOWNORMAL = 1


Private Type STARTUPINFO
      cb                                     As Long
      lpReserved                             As String
      lpDesktop                              As String
      lpTitle                                As String
      dwX                                    As Long
      dwY                                    As Long
      dwXSize                                As Long
      dwYSize                                As Long
      dwXCountChars                          As Long
      dwYCountChars                          As Long
      dwFillAttribute                        As Long
      dwFlags                                As Long
      wShowWindow                            As Integer
      cbReserved2                            As Integer
      lpReserved2                            As Long
      hStdInput                              As Long
      hStdOutput                             As Long
      hStdError                              As Long
End Type


Private Type PROCESS_INFORMATION
      hProcess                               As Long
      hThread                                As Long
      dwProcessId                            As Long
      dwThreadID                             As Long
End Type

Private Declare Function _
            WaitForSingleObject Lib "kernel32" ( _
                                ByVal hHandle As Long, _
                                ByVal dwMilliseconds As Long) As Long

Private Declare Function _
            CreateProcessA Lib "kernel32" ( _
                           ByVal lpApplicationName As Long, ByVal lpCommandLine As String, _
                           ByVal lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
                           ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
                           ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
                           lpStartupInfo As STARTUPINFO, _
                           lpProcessInformation As PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Public Sub msiShellAndWait(ByVal CommandLine As String, _
                           ByVal bShowWindow As Boolean)
      Dim ReturnValue                        As Long
      Dim Start                              As STARTUPINFO
      Dim Process                            As PROCESS_INFORMATION
      ' Initialize the STARTUPINFO structure:
      Start.cb = Len(Start)
      If bShowWindow = False Then
            Start.dwFlags = STARTF_USESHOWWINDOW
            Start.wShowWindow = SW_HIDE
      End If
      ' Start the shelled application:
      ReturnValue = CreateProcessA(0&, CommandLine, 0&, 0&, _
                                   1&, NORMAL_PRIORITY_CLASS, 0&, 0&, Start, Process)
      ' Wait for the shelled application to finish:
      ReturnValue = WaitForSingleObject(Process.hProcess, INFINITE)
      ReturnValue = CloseHandle(Process.hProcess)
End Sub



« back
Share this page:
Home Sitemap Search List of all utilities Printer friendly page ^ Back to Top
© 1999-2017 - - - - The Netherlands

Connect with us and receive our tips that show you how to benefit from the tools in ASAP Utilities at:
Follow us on TwitterFollow us on Facebook