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, Commented May 7, 2018 at 15:05
@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
– QHarr, Commented May 7, 2018 at 15:14
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
– QHarr, Commented May 7, 2018 at 15:15
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
– omegastripes, Commented May 7, 2018 at 15:18
Hah...it works with the line to call added as simply ExcelMacroExample at the top. How stupid of me :-( @omegastripes Thank you. — QHarr
– QHarr, Commented May 7, 2018 at 15:23

William Walseth · Accepted Answer · 2018-05-07 13:17:07Z

5

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

edited May 7, 2018 at 13:17

answered May 7, 2018 at 13:13

William Walseth

2,9431 gold badge28 silver badges26 bronze badges

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

6 Comments

QHarr Over a year ago

Maybe I am misunderstanding, if so I apologise, but I can right click and run and I get the vba msgbox displayed.This line xlApp.Run "MessageMe" should run the macro after it is imported to the excel project with xlApp.VBE.ActiveVBProject.VBComponents.Import "C:\Users\User\Desktop\Test.bas"

QHarr Over a year ago

If I do WScript.Echo I am not using the excel macro to produce the message box; which is my intention.

William Walseth Over a year ago

Sorry I corrected my post, MsgBox does work from CScript (news to me). I think you just need something to call your ExcelMacroExample code from the 1st VBS script that your running from the command line.

QHarr Over a year ago

I tried that. I should have added to my question. Sorry. Same result.

William Walseth Over a year ago

I wonder if it's a permissions issue? Specifically creating the Excel object. You might wrap that CreateObject call in a try catch or step through it to see what's going on.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related