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

~~~~~~~~~~~~

1. User selects some files from the HTA/GUI.
2. 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)
3. 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

~~~~~~~~~~~~

1. myScript.vbs opens up the first 3 arguments which are Excel files. One of them is a *.xlsm file that has my VBA macro.

2. 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)
   ...
   

3. 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 <arg1> <arg2> <arg3> <arg4>
    '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.

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 <path>\myScript.vbs " & 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"" " & File, 1, true

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2:Win32_Process")
objWMIService.Create "notepad.exe", null, null, intProcessID
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\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."

Probably something like this? (UNTESTED)

Sub Sample()
    Dim strWB4, strMyMacro
    strMyMacro = "Sheet1.my_macro_name"

    '
    '~~> 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

    '
    '~~> Rest of Code
    '
End Sub

'~~> 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