Command line execution of VBScript file to execute VBA macro failing to produce msgbox

Question

Situation:

I am attempting to run a VBScript from windows command line. The script opens excel, imports a .bas file and runs the macro within. The macro should then display a VBA MsgBox.

If I right click on the .vbs file, on the desktop, and select Run VBScript, it works perfectly.

If I execute the following from the command line, in the correct location (i.e. desktop), no MsgBox is displayed though everything appears to run without error.
```
Test.vbs
```

What I have tried:

I have looked at lots of articles some of which I list as references. In particular, I worked my way through this answer. I had already tried things like:

CScript Test.vbs
WScript Test.vbs
CScript //logo Test.vbs
WScript //nologo Test.vbs

Following on from reading that answer, I then confirmed that Windows knew how to launch .vbs files, which seemed self-evident as I could right-click and run.

So:

1) Running assoc .vbs gave me the expected:

:\Windows\System32>assoc .vbs
.vbs=VBSFile

2) Running ftype VBSFile gave the expected:

VBSFile="%SystemRoot%\System32\WScript.exe" "%1" %*

3) I tried adding Pause and wait times to the command line string to no avail.

4) I have added various flags including debugging e.g. CScript //X , no difference.

Question:

How do I run my VBScript from the command line and get the msgbox to appear?

VBSCript (Test.vbs)

Option Explicit

Sub ExcelMacroExample() 
  Dim xlApp 
  Dim xlBook 
  Set xlApp = CreateObject("Excel.Application") 
  Set xlBook = xlApp.Workbooks.Open("C:\Users\User\Desktop\Test.xlsb", 0, True) 
  xlApp.VBE.ActiveVBProject.VBComponents.Import "C:\Users\User\Desktop\Test.bas"
  xlApp.DisplayAlerts = False
  xlApp.Run "MessageMe"
  xlApp.Wait Now + TimeSerial(0,0,2)
  'xlApp.Quit 
  xlApp.DisplayAlerts = True
 ' Set xlBook = Nothing 
 ' Set xlApp = Nothing 
End Sub

Test.bas

Public Sub MessageMe
   Msgbox "Hello"
End Sub

Notes:

All files reside on Desktop folder.

References:

How do you call Sub ExcelMacroExample() in the script, seems that isn't full code? Try to add the line MsgBox WScript.Path to the script just to check the bitness and make sure that it doesn't affect execution. — omegastripes
@omegastripes Forgive my ignorance in this field. That I am afraid is the whole script! I should be putting MsgBox WScript.Path into the VBScript? Seems wrong. So guess I have misunderstood. MsgBox WScript.Path in the Test.bas makes no difference. — QHarr
I simply want to command line execute Test.vbs > that opens Excel > imports Test.bas > Runs "MessageMe" macro that has now been imported > That generates a msgbox. — QHarr
I just mean that it is not clear how Sub ExcelMacroExample() is being launched. There is no ExcelMacroExample call within the code. And yes, put MsgBox WScript.Path into the script, and check if the path is the same when you run VBS file via command line and right click. — omegastripes
Hah...it works with the line to call added as simply ExcelMacroExample at the top. How stupid of me :-( @omegastripes Thank you. — QHarr

William Walseth William Walseth · Accepted Answer · 2018-05-07T13:13:40

In your main VBS, your code in MessageMe won't run. You'll need to include code to call your MessageMe Sub.

Also, in cscript, most people use instead of MsgBox, but apparently (new to me) MsgBox pops-up too...

WScript.Echo "From Main"

Call MessageMe

Public Sub MessageMe
    ' WScript.Echo "Hello"
    MsgBox "Hello"
End Sub

Command line execution of VBScript file to execute VBA macro failing to produce msgbox

1 Answers