VBScript - How to make program wait until process has finished

Question

I have a problem in a VBScript that I am using with a VBA Excel macro and a HTA  The problem is just the VBScript  I have the other two components  i e  the VBA macro and HTA front-end working perfectly  But before I explain the problem  I think for you to help me I must help you understand the context of the VBScript   So  basically all components  VBScript  VBA macro and HTA  are parts of a tool that I am building to automate some manual chores  It pretty much goes like this   A - HTA                 User selects some files from the HTA GUI  Within the HTML of the HTA there is some VBScript within the  SCRIPT  tags which passes the users 4 input files as arguments to a VBScript  executed by WScript exe - you may refer to note  1 for clarity here  The script  lets call it myScript vbs from now on then handles the 4 arguments  3 of which are specific files and the 4th is a path folder location that has multiple files in it -  also see note  2 for clarity    B - myScript vbs                 myScript vbs opens up the first 3 arguments which are Excel files  One of them is a   xlsm file that has my VBA macro  myScript vbs then uses the 4th argument which is a PATH to a folder that contains multiple files and assigns that to a variable for passing to a FileSystemObject object when calling GetFolder  i e        Other code here  irrelevant for this post Dim FSO  FLD  strFolder      Other code here  irrelevant for this post arg4   args Item 3  strFolder   arg4 Set FSO   CreateObject  Scripting FileSystemObject   Get a reference to the folder you want to search Set FLD   FSO GetFolder strFolder       From here I create a loop so that I can sequentially open the files within the folder and then run my macro  i e       Dim strWB4  strMyMacro strMyMacro    Sheet1 my macro name    loop through the folder and get the file names For Each Fil In FLD Files      Set x4WB   x1 Workbooks Open Fil  x4WB Application Visible   True  x1 Run strMyMacro  x4WB close Next         Please note that when the first 3 Excel files have opened  controlled by code prior to the loop  and not shown here as I am having no problem with that part  I must keep them open   It is the files in the folder  that was passed as the 4th argument  which must sequentially open and close  But inbetween opening and closing  I require the VBA macro  wrote in one of the 3 Excel files previously opened  to run each time the loop iterates and opens a new file from the folder  I hope you follow - if not please let me know        The problem I am having is that the files in the folder open and close  open and close  n number of times  n     of files in folder  naturally  without waiting for the macro to run  This is not what I want  I have tried the WScript sleep statement with a 10 second delay after the  x1 Run strMyMacro  statement  but to no avail   Any ideas   Thanks  QF   NOTES   1 - For simplicity clarity this is how       strCMD   cmd  c C  windows system32 wscript exe myScript vbs  lt arg1 gt   lt arg2 gt   lt arg3 gt   lt arg4 gt       FYI - This is run by creating a WShell object  wsObj  and using the  run method  i e  WShell run strCMD    2 The HTA employs a piece of JavaScript that strips the users 4th input file  HTML  INPUT TYPE  file   and passes that to the the VBScript within the HTA  This gets me round the problem of not being able to exclusively select a FOLDER in HTML

User · Answer

Probably something like this   UNTESTED   Sub Sample       Dim strWB4  strMyMacro     strMyMacro    Sheet1 my macro name                 gt  Rest of Code             loop through the folder and get the file names     For Each Fil In FLD Files         Set x4WB   x1 Workbooks Open Fil          x4WB Application Visible   True          x1 Run strMyMacro          x4WB Close          Do Until IsWorkBookOpen Fil    False             DoEvents         Loop     Next                gt  Rest of Code       End Sub      gt  Function to check if the file is open Function IsWorkBookOpen FileName As String      Dim ff As Long  ErrNo As Long      On Error Resume Next     ff   FreeFile       Open FileName For Input Lock Read As  ff     Close ff     ErrNo   Err     On Error GoTo 0      Select Case ErrNo     Case 0     IsWorkBookOpen   False     Case 70    IsWorkBookOpen   True     Case Else  Error ErrNo     End Select End Function

User · Answer

This may not specifically answer your long 3 part question but this thread is old and I found this while searching today  Here is one shorter way to   Wait until a process has finished   If you know the name of the process such as  EXCEL EXE   strProcess    EXCEL EXE      Set objWMIService   GetObject  winmgmts  impersonationLevel impersonate      root cimv2   Set colProcesses   objWMIService ExecQuery   Select   from Win32 Process Where Name      amp  strProcess  amp      Do While colProcesses Count  gt  0     Set colProcesses   objWMIService ExecQuery   Select   from Win32 Process Where Name      amp  strProcess  amp          Wscript Sleep 1000   Sleep 1 second     msgbox colProcesses count  optional to show the loop works Loop   Credit to  http   crimsonshift com scripting-check-if-process-or-program-is-running-and-start-it

User · Answer

You need to tell the run to wait until the process is finished  Something like   const DontWaitUntilFinished   false  ShowWindow   1  DontShowWindow   0  WaitUntilFinished   true set oShell   WScript CreateObject  WScript Shell   command    cmd  c C  windows system32 wscript exe  lt path gt  myScript vbs    amp  args oShell Run command  DontShowWindow  WaitUntilFinished   In the script itself  start Excel like so  While debugging start visible   File    c  test myfile xls  oShell run    C  Program Files Microsoft Office Office14 EXCEL EXE      amp  File  1  true

User · Answer

strComputer       Set objWMIService   GetObject  winmgmts      amp  strComputer  amp    root cimv2 Win32 Process   objWMIService Create  notepad exe   null  null  intProcessID Set objWMIService   GetObject  winmgmts      amp  strComputer  amp    root cimv2   Set colMonitoredProcesses   objWMIService ExecNotificationQuery         Select   From   InstanceDeletionEvent Within 1 Where TargetInstance ISA  Win32 Process    Do Until i   1     Set objLatestProcess   colMonitoredProcesses NextEvent     If objLatestProcess TargetInstance ProcessID   intProcessID Then         i   1     End If Loop Wscript Echo  Notepad has been terminated

[vba] VBScript - How to make program wait until process has finished?

Examples related to vba

Examples related to vbscript

Examples related to excel

Examples related to excel-2010