Open Excel and Run Macro using VBScript [duplicate]

Question

I'm trying to use this VBScript to open a file and run a macro. Ideally I'd open it from my personal workbook, but happy to run from anywhere as a compromise.

Option Explicit

On Error Resume Next

RunExcelMacro

Sub RunExcelMacro()

Dim xlApp
Dim xlBook

If CheckAppOpen("excel.application")  Then
       'MsgBox "App Loaded"
        Set xlApp = GetObject(, "Excel.Application")   
Else
        ' MsgBox "App Not Loaded"
        Set  xlApp = CreateObject(,"Excel.Application")   
End If

xlApp.visible = True
set xlBook = xlApp.Workbooks.Open ("C:\Users\....\PERSONAL.xlsb", 0, True)
xlApp.Run "Module1.My Macro"
xlApp.Quit()

xlBook = Nothing
xlApp = Nothing

End Sub

The script does not open any Excel file, independent of if it being on a server or in my C drive.

There's been a fair number of posts on this. I did some research.

I tried:

Set xlApp = GetObject("Excel.Application")  
Set xlApp = CreateObject("Excel.Application")

as well as

Dim Filename as string
Filename = "C:/....."
set xlBook = xlApp.Workbooks.Open (Filename)

I've tried opening .xls, .xlsm, .xlsb all to no avail.

I am not getting any error messages. The cmd console opens and then closes with this

Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

Remove On Error Resume Next and run this from a cmd window (cscript.exe your.vbs) to see the error messages. — Alex K.
– Alex K., Commented Sep 20, 2016 at 11:35
Thanks! I'm now getting : " Microsoft VBScript runtime error: Type mismatch" for the line: Set xlApp = CreateObject(, "Excel.Application") — REdim.Learning
– REdim.Learning, Commented Sep 20, 2016 at 11:45
Remove the leading comma: Set xlApp = CreateObject("Excel.Application") — Alex K.
– Alex K., Commented Sep 20, 2016 at 11:46
Wizard, it's now opening the file, but the macro isn't running. "Microsoft Excel: Cannot run the macro 'Macro1.My_macro'. The macro may not be available in this workbook or all macros may be disabled." — REdim.Learning
– REdim.Learning, Commented Sep 20, 2016 at 11:48
Macronames cannot have spaces in them, you are trying to run a macro named "My Macro", which is an illegal name for a sub. — jkpieterse
– jkpieterse, Commented Sep 20, 2016 at 12:07

REdim.Learning · Accepted Answer · 2016-09-20 12:07:50Z

2

Thanks to @AlexK. Turned out to be a comma in the wrong place.

Also thanks to this post changed:

 xlApp.Run "Module1.MyMacro"

to:

   xlApp.Run xlBook.name & "!Module1.MyMacro"

answered Sep 20, 2016 at 12:07

REdim.Learning

6653 gold badges16 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Open Excel and Run Macro using VBScript [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related