CODEWITHSUNDEEP
excelvbscriptvba
QHarr
QHarr

Reputation: 84465

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

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.

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

Right click and run VBScript

  1. 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:

  1. Running Scripts from the Command Line
  2. VBS file doesn't run through cmd prompt
  3. Running Scripts from the Command Prompt
  4. How to run VBScript from command line without Cscript/Wscript

Upvotes: 2

Views: 3273

Answers (1)

William Walseth
William Walseth

Reputation: 2923

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

Upvotes: 5

Related Questions