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.

我在使用VBA / Excel宏和HTA的VBScript中遇到问题。问题只是VBScript，我还有其他两个组件，即VBA宏和HTA前端工作正常。但在我解释这个问题之前，我想你帮助我，我必须帮助你理解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:

因此，基本上所有组件（VBScript，VBA宏和HTA）都是我正在构建的工具的一部分，以自动化一些手动琐事。它几乎是这样的：

A - HTA

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

User selects some files from the HTA/GUI.
用户从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)
在HTA的HTML中，“SCRIPT”标签中有一些VBScript，它将用户的4个输入文件作为参数传递给VBScript（由WScript.exe执行 - 为了清楚起见，您可以参考注释＃1）
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)
该脚本，从现在开始调用myScript.vbs然后处理4个参数，其中3个是特定文件，第4个是包含多个文件的路径/文件夹位置 - （为清晰起见，请参阅注释＃2）

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打开前3个参数，这些参数是Excel文件。其中一个是具有我的VBA宏的* .xlsm文件。
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.

myScript.vbs然后使用第4个参数作为包含多个文件的文件夹的PATH，并将其分配给变量，以便在调用GetFolder时传递给FileSystemObject对象，即
```
... '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.

请注意，当前3个Excel文件打开时（由循环前的代码控制，此处未显示，因为我对该部分没有任何问题）我必须保持它们打开。

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

它是文件夹中的文件（作为第4个参数传递），必须按顺序打开和关闭。但是在打开和关闭之间，我需要VBA /宏（在先前打开的3个Excel文件中的一个中写入）在每次循环迭代时运行并从文件夹中打开一个新文件（我希望你关注 - 如果没有请让我知道:)）。

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.

我遇到的问题是文件夹中的文件打开和关闭，打开和关闭n次（n =文件夹中的文件数，当然），而不等待宏运行。这不是我想要的。我在'x1.Run strMyMacro'语句之后尝试了WScript.sleep语句，延迟了10秒，但无济于事。

Any ideas?

有任何想法吗？

Thanks, QF.

谢谢，QF。

NOTES:

笔记：

1 - For simplicity/clarity this is how:

1 - 为简单/清晰起见，这是如何：

    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.

2 HTA使用一段JavaScript来剥离用户的第四个输入文件（HTML：INPUT TYPE =“file”）并将其传递给HTA中的VBScript。这让我解决了无法在HTML中专门选择FOLDER的问题。

4 个解决方案

#1

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:

在脚本本身，像这样启动Excel。调试启动时可见：

File = "c:\test\myfile.xls"
oShell.run """C:\Program Files\Microsoft Office\Office14\EXCEL.EXE"" " & File, 1, true

#2

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."

#3

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"

这可能没有具体回答你的长篇3部分问题，但这个帖子已经过时了，我今天在搜索时发现了这个问题。这是一个较短的方法：“等到一个过程完成。”如果您知道进程的名称，例如“EXCEL.EXE”

strProcess = "EXCEL.EXE"    
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set colProcesses = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = '"& strProcess &"'")
Do While colProcesses.Count > 0
    Set colProcesses = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = '"& strProcess &"'")
    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/

感谢：http：//crimsonshift.com/scripting-check-if-process-or-program-is-running-and-start-it/

#4

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

#1