Executing VBScript file from Excel VBA macros

Question

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

Can't you simply run the code directly in VBA? VBScript is a sub-set of VB and can run almost unmodified in a VB environment. — Tomalak, Commented Jun 4, 2011 at 17:19
See the requirement is i need to assign dynamic values(basically file paths and filenames) to the variables in Excel macros. I dont want to go in to the macros each time i change the path, so if we can assign those values through the VBS then it would be easy assign and change the values. — S.., Commented Jun 4, 2011 at 17:23
I agree with @Tomalak. I am not aware of anything that VBS can do that Excel VBA cannot do, and usually better. If this is a matter of external parameter control, then I would suggest using Excel cell values for that purpose. Or an INI like text file. Or registry settings. Or the Active Directory. Or, ... — RBarryYoung, Commented Jun 4, 2011 at 18:27
@S.. Please format your code as code, otherwise it's a real pain to read. I'll do it for you this time. — Jean-François Corbett, Commented Jun 6, 2011 at 11:56

user688334user688334 · Accepted Answer · 2011-06-05 00:56:56Z

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.

thanks for your effort and reply....which made me learn few other concepts in VBA but i am looking for 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 — S.., Commented Jun 5, 2011 at 16:05
Correct me if I'm wrong but the code you posted would only work with 1 instance of excel running? The way you have GetObject() seems that would be the case. If so is there a way to be more accurate in getting the Excel instance that fired off the vbscript? — user441521, Commented Feb 4, 2012 at 21:10
Well the script states the sheet and the cell; just amend to state a specific workbook. — user688334, Commented Mar 1, 2012 at 0:00
if you provide the full path to the workbook as the only parameter to GetObject then you can have more than one instance of excel running — almog.ori, Commented Aug 17, 2012 at 10:31

Collectives™ on Stack Overflow

Executing VBScript file from Excel VBA macros

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related