title: VBScript - How to make program wait until process has finished?
body: <p>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.</p>
  
  <p>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:</p>
  
  <h1>A - HTA</h1>
  
  <p>~~~~~~~~~~~~</p>
  
  <ol>
  <li>User selects some files from the HTA/GUI.</li>
  <li>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)</li>
  <li>The script, lets call it <em>myScript.vbs</em> 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)</li>
  </ol>
  
  <h1>B - myScript.vbs</h1>
  
  <p>~~~~~~~~~~~~</p>
  
  <ol>
  <li>myScript.vbs opens up the first 3 arguments which are Excel files. One of them is a *.xlsm file that has my VBA macro.</li>
  <li><p>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.</p>
  
  <pre><code>... '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)
  ...
  </code></pre></li>
  <li><p>From here I create a loop so that I can sequentially open the files within the folder
  and then run my macro, i.e.</p>
  
  <pre><code>...
  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 
  ...
  </code></pre></li>
  </ol>
  
  <p>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.</p>
  
  <p>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) <strong>to run each time</strong> the loop iterates and opens a new file from the folder (I hope you follow - if not please let me know :) ).</p>
  
  <p>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.</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks,
  QF.</p>
  
  <p>NOTES:</p>
  
  <p>1 - For simplicity/clarity this is how:</p>
  
  <pre><code>    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)
  </code></pre>
  
  <p>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.</p>