VBA: Cannot implement Excel VBS outside of excel

Question

I have the code shown below, which opens a workbook and runs the corresponding macro. Running this script through excel works just fine, but running the .vbs file with the same code does not run the macro. I have tried both double clicking the file and running it through the cmd prompt with "cscript.exe LaunchMacro.vbs". In addition, using WScript.Echo does not print to my command line. Am i missing something here?

Thank you!

Option Explicit

Sub LaunchMacro()
Dim xlApp, xlBook    
Dim oShell: Set oShell = CreateObject("WScript.Shell")    
oShell.CurrentDirectory = "H:"   
Set xlApp = CreateObject("Excel.Application")    
xlApp.Visible = True   
xlApp.Application.Visible = False

Set xlBook = xlApp.Workbooks.Open("H:\SW Tool Resources\test\tester.xlsm")
MsgBox ("File Opened")
xlApp.DisplayAlerts = False
xlApp.Application.Run ("tester.xlsm!Module3.split")
MsgBox ("Application Should Have Run")
xlBook.Saved = True
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

End Sub

Even though there are obvious similarities vbs is not the same as VBA (visual basic for applications), unfortunately, this will not be as easy as copying and pasting the code from Excel VBA. — ChrisProsser
Can I ask what is the motivation for moving the code into a .vbs script? — ChrisProsser

stenci stenci · Accepted Answer · 2013-06-28T16:46:09

VBS and VBA are different.

Try to put only this code in the vbs file and run it:

Set xlApp = CreateObject("Excel.Application")    
xlApp.Visible = True   
xlApp.Application.Visible = False

Set xlBook = xlApp.Workbooks.Open("H:\SW Tool Resources\test\tester.xlsm")
MsgBox ("File Opened")
xlApp.DisplayAlerts = False
xlApp.Application.Run ("tester.xlsm!Module3.split")
MsgBox ("Application Should Have Run")
xlBook.Saved = True
xlApp.Quit

I didn't test it, and very likely will not succeed. But it should put it in the right direction.

Edit:

Here is something that should help you getting started with VBS: a script can define some functions, but only runs them if you call them.

For example this will only run Sub1:

Sub Sub1()
  MsgBox "1"
End Sub
Sub Sub2()
  MsgBox "2"
End Sub
Sub1

VBA: Cannot implement Excel VBS outside of excel

1 Answers