How to Run VBS script from cmd?

Question

I have the following vbs script from Microsoft Support for adding Excel add-in:

Dim oXL As Object
Dim oAddin As Object
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Set oAddin = oXL.AddIns.Add("c:\Program Files\MyApp\MyAddin.xla",  True)
oAddin.Installed = True
oXL.Quit
Set oXL = Nothing

I save the above script to a file called as addin.vbs and run it from a command console:

C:\...>cscript addin.vbs

I got the following error:

c:\...\addin.vbs(1, 9) Microsoft VBScript compilation error: Expected end of statement

Not sure how I can run it from cmd console?

I am running it from Windows XP.

Answer 1

Visual Basic for Applications (VBA, which your code is written in) and Visual Basic Scripting Edition (VBS) are not the same language.

Windows Scripting Host (WSH, i.e. cscript.exe and wscript.exe) only handles Active Scripting languages (in most installs, VBScript and JScript). VBA can only be run within the application that is destined to host it.

Just follow the directions on the Microsoft Support page you have and add the script to Excel.

Answer 2

The error occurs because of the As Object clause. Unlike VBA, VBScript has only one data type — Variant, so you don't specify the data type when declaring a variable. Remove the As Object clauses and the script should work fine:

Dim oXL, oAddin
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Set oAddin = oXL.AddIns.Add("c:\Program Files\MyApp\MyAddin.xla",  True)
oAddin.Installed = True
oXL.Quit
Set oXL = Nothing