从Excel VBA宏执行VBScript文件 [英] Executing VBScript file from Excel VBA macros

问题描述

我需要一些excel vba示例，在VBA代码（Excel宏）中，我可以调用VBScript，并从vbscript获取一些值，如文件名和目录信息，并将其分配给VBA代码中的变量。
谢谢你提前

有些这样的事情

VBA宏：

  Sub Foo2Script 
 Dim x As Long 
x = 2 
'在这里调用VBsc 
 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中以独立运行。例如，您可以在VBA继续使用其他代码时，安排一个群组的个人VBScripts从网站大量下载。

以下是我简化的一些VBA代码显示可以做些什么，并在飞行中写一个简单的VBScript。通常我喜欢使用'wshShell.Run& SFilename& 这意味着我可以忘记它，但我已经在这个例子中包含这个方法 Set proc = wshShell.exec（strexec）这允许对象的测试完成

将其放在MODULE1中

  Option Explicit 
 
公共路径As String 
 
 Sub writeVBScript（）
 Dim s As String，SFilename As String 
 Dim intFileNum As Integer，wshShell作为对象，proc As Object 
 
 
 Dim test1 As String 
 Dim test2 As String 
 
 test1 =VBScriptMsg  -  Test1是此变量
 test2 =VBScriptMsg  -  Test2是该变量
 
写入VBScript（写入Excel Sheet1！A1和调用函数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 
 
'将VBScript文件写入磁盘
 SFilename = ActiveWorkbook.path& \TestVBScript.vbs
 intFileNum = FreeFile 
打开SFilename输出为intFileNum 
打印#intFileNum，s 
关闭intFileNum 
 DoEvents 
 
'运行VBScript文件
设置wshShell = CreateObject（Wscript.Shell）
设置proc = wshShell.exec（cscript& SFilename&）'运行VBScript 
 '也可以发送一些变量
'设置proc = wsh.Exec（cscript VBScript.vbs var1 var2）'运行VBScript传递变量
 
'等待脚本结束
 Do While proc.Status = 0 
 DoEvents 
 Loop 
 
 MsgBox（This is in Excel：& Sheet1.Range（A1））
 MsgBox （从VBScript通过：&路径）
 
'wshShell.Run& SFilename& 
 
杀死ActiveWorkbook.path& \TestVBScript.vbs
 End Sub 
 
 
公共函数ReturnVBScript（strText As String）
 path = strText 
结束函数
  

这展示了可以传递变量的几种方法。

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

Some thing like this

VBA macro:

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

解决方案

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.

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

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屋！