从 Excel VBA 宏执行 VBScript 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6238215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing VBScript file from Excel VBA macros
提问by S..
I need some excel vba examples, where with in the VBA code(Excel Macro) i could call a VBScript and will get some values like filename and directory information from the vbscript and assign it to the variables in VBA code. Thank you in advance
我需要一些 excel vba 示例，在 VBA 代码（Excel 宏）中，我可以调用 VBScript 并从 vbscript 获取一些值，如文件名和目录信息，并将其分配给 VBA 代码中的变量。先感谢您
Some thing like this
像这样的事情
VBA macro:
VBA宏：
Sub Foo2Script
Dim x As Long
x=2
'Call VBscript here
MsgBox scriptresult
End Sub
VBScript:
脚本：
Dim x, y ,Z
x = x_from_macro
y = x + 2
Z = X+Y
scriptresult = y,Z
回答by osknows
It can be done but I would have to agree with Tomalak and others that it's not the best way to go. However, saying that, VBScript can work wonders occasionally if you use it as a kind of fire and forget mechanism. It can be used quite effectively to simulate multi-threading in VBA whereby you breakdown the payload and farm it out to individual VBScripts to run independently. Eg you could arrange a "swarm" of individual VBScripts to mass download from websites in the background whilst VBA continues with other code.
这是可以做到的，但我不得不同意 Tomalak 和其他人的看法，这不是最好的方法。但是，话虽如此，如果您将 VBScript 用作一种即发即忘的机制，它偶尔会产生奇迹。它可以非常有效地用于模拟 VBA 中的多线程，您可以分解有效负载并将其分配给单独的 VBScripts 以独立运行。例如，您可以安排一个“群”的单个 VBScripts 在后台从网站大量下载，而 VBA 继续使用其他代码。
Below is some VBA code I've simplified to show what can be done and writes a simple VBScript on the fly. Normally I prefer to run it using 'wshShell.Run """" & SFilename & """"which means I can forget about it but I've included in this example this method Set proc = wshShell.exec(strexec)which allows a test of the object for completion
下面是我简化的一些 VBA 代码，以展示可以完成的操作并即时编写一个简单的 VBScript。通常我更喜欢使用'wshShell.Run """" & SFilename & """"它来运行它，这意味着我可以忘记它，但我在这个例子中包含了这个方法Set proc = wshShell.exec(strexec)，它允许测试对象以完成
Put this in MODULE1
把它放在 MODULE1 中
Option Explicit
Public path As String
Sub writeVBScript()
Dim s As String, SFilename As String
Dim intFileNum As Integer, wshShell As Object, proc As Object
Dim test1 As String
Dim test2 As String
test1 = "VBScriptMsg - Test1 is this variable"
test2 = "VBScriptMsg - Test2 is that variable"
'write VBScript (Writes to Excel Sheet1!A1 & Calls Function Module1.ReturnVBScript)
s = s & "Set objExcel = GetObject( , ""Excel.Application"") " & vbCrLf
s = s & "Set objWorkbook = objExcel.Workbooks(""" & ThisWorkbook.Name & """)" & vbCrLf
s = s & "Set oShell = CreateObject(""WScript.Shell"")" & vbCrLf
s = s & "Msgbox (""" & test1 & """)" & vbCrLf
s = s & "Msgbox (""" & test2 & """)" & vbCrLf
s = s & "Set oFSO = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf
s = s & "oShell.CurrentDirectory = oFSO.GetParentFolderName(Wscript.ScriptFullName)" & vbCrLf
s = s & "objWorkbook.sheets(""Sheet1"").Range(""" & "A1" & """) = oShell.CurrentDirectory" & vbCrLf
s = s & "Set objWMI = objWorkbook.Application.Run(""Module1.ReturnVBScript"", """" & oShell.CurrentDirectory & """") " & vbCrLf
s = s & "msgbox(""VBScriptMsg - "" & oShell.CurrentDirectory)" & vbCrLf
Debug.Print s
' Write VBScript file to disk
SFilename = ActiveWorkbook.path & "\TestVBScript.vbs"
intFileNum = FreeFile
Open SFilename For Output As intFileNum
Print #intFileNum, s
Close intFileNum
DoEvents
' Run VBScript file
Set wshShell = CreateObject("Wscript.Shell")
Set proc = wshShell.exec("cscript " & SFilename & "") ' run VBScript
'could also send some variable
'Set proc = wsh.Exec("cscript VBScript.vbs var1 var2") 'run VBScript passing variables
'Wait for script to end
Do While proc.Status = 0
DoEvents
Loop
MsgBox ("This is in Excel: " & Sheet1.Range("A1"))
MsgBox ("This passed from VBScript: " & path)
'wshShell.Run """" & SFilename & """"
Kill ActiveWorkbook.path & "\TestVBScript.vbs"
End Sub
Public Function ReturnVBScript(strText As String)
path = strText
End Function
This demonstrated several ways that variables can be passed around.
这展示了变量可以传递的几种方式。
