Is it possible to call a .vbs script from a VBA code whenever needed? If possible then can you give me a sample code of how to do so?
-
possible duplicate of Executing VBScript file from Excel VBA macrosJean-François Corbett– Jean-François Corbett2014-11-22 20:36:26 +00:00Commented Nov 22, 2014 at 20:36
-
As it is a duplicate, I voted to close it as a duplicate.Jean-François Corbett– Jean-François Corbett2014-11-23 10:44:09 +00:00Commented Nov 23, 2014 at 10:44
Add a comment
|
4 Answers
To run a file:
Shell "wscript c:\null\a.vbs", vbNormalFocus
replacing wscript with cscript if the VBS wants to use the console.
Or you can add a reference to the Microsoft Script Control and interact with the VBScript runtime directly to execute VBS code, procedures etc;
Dim scr As ScriptControl: Set scr = New ScriptControl
scr.Language = "VBScript"
scr.AddCode "sub T: msgbox ""All Hail Cthulhu"": end sub"
scr.Run "T"
6 Comments
Arup Rakshit
Hi Alex, i want to call a .vbs file from VBA... so how to do that? this was my question..Can you clarify your suggestion please?
Arup Rakshit
Sub test() Dim scr As ScriptControl: Set scr = New ScriptControl scr.Language = "VBScript" scr.AddCode "sub T: msgbox ""All Hail Cthulhu"": end sub" scr.Run "T" End Sub
Arup Rakshit
getting error : Compiler error: "User-defined type not defined"
Alex K.
The
shell line runs a vbs ... To avoid User-defined type not defined you must click tools -> references in the VBA IDE & tick Microsoft Script Control 1.0
Jarekczek
Alex, would you mind adding one more detail to your answer? That the object may also be created using
set scr = CreateObject("MSScriptControl.ScriptControl"), which is the only option in some environments. |
I only want to add to Alex' answer, that in some environments the object must be created in the following way:
set scr = CreateObject("MSScriptControl.ScriptControl")
If Alex adds this to his answer, I will delete this one.
Comments
Try somthing like this
ChDir ThisWorkbook.Path
Shell "wscript " & ThisWorkbook.Path & "\your.vbs", vbNormalFocus
It helped me.
Comments
You may want to use """ if your path name contains a - For me the following solved this issue:
Shell "cscript """ & ActiveWorkbook.Path & """\your.vbs", vbNormalFocus
