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
– ChrisProsser, Commented Jun 28, 2013 at 16:39
Can I ask what is the motivation for moving the code into a .vbs script? — ChrisProsser
– ChrisProsser, Commented Jun 28, 2013 at 16:40

stenci · Accepted Answer · 2013-06-28 16:51:09Z

1

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

edited Jun 28, 2013 at 16:51

answered Jun 28, 2013 at 16:46

stenci

8,55914 gold badges69 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ansgar Wiechers Over a year ago

+1 The issue is most likely that the procedure is never called. I'd remove the line xlApp.Application.Visible = False, though. xlApp.Visible and xlApp.Application.Visible are the exact same property. It's pointless to first display the application and then hide it again in the next step. Also I'd set xlApp.DisplayAlerts = True while debugging.

stenci Over a year ago

I would add to Ansgar comment: when you make xlApp.Visible = True Excel loads the interface (which is slow) and every operation from now on will update the interface (which is slow). I would keep it visible during the debugging process, then leave it non visible when everything works well.

Collectives™ on Stack Overflow

VBA: Cannot implement Excel VBS outside of excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related