从 Excel VBA 宏执行 VBScript 文件 [英] Executing VBScript file from Excel VBA macros
问题描述
我需要一些 excel vba 示例,在 VBA 代码(Excel 宏)中,我可以调用 VBScript 并从 vbscript 中获取一些值,例如文件名和目录信息,并将其分配给 VBA 代码中的变量.提前谢谢您
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
类似这样的事情
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
推荐答案
它可以做到,但我不得不同意 Tomalak 和其他人的看法,这不是最好的方法.但是,话虽如此,如果您将 VBScript 用作一种即发即忘的机制,它偶尔会产生奇迹.它可以非常有效地用于模拟 VBA 中的多线程,您可以分解有效负载并将其分配给单独的 VBScripts 以独立运行.例如,您可以安排一个群"的单个 VBScripts 在后台从网站大量下载,而 VBA 继续使用其他代码.
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.
下面是我简化的一些 VBA 代码,以展示可以完成的操作并即时编写一个简单的 VBScript.通常我更喜欢使用 'wshShell.Run """" & 运行它S文件名&""""
这意味着我可以忘记它,但我在这个例子中包含了这个方法 Set proc = wshShell.exec(strexec)
它允许测试对象以完成
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
把它放在 MODULE1 中
Put this in 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.
这篇关于从 Excel VBA 宏执行 VBScript 文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!