3

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.

Community
  • 1
  • 1
REdim.Learning
  • 655
  • 2
  • 14
  • 32
  • 1
    Remove `On Error Resume Next` and run this from a cmd window (`cscript.exe your.vbs`) to see the error messages. – Alex K. Sep 20 '16 at 11:35
  • Thanks! I'm now getting : " Microsoft VBScript runtime error: Type mismatch" for the line: Set xlApp = CreateObject(, "Excel.Application") – REdim.Learning Sep 20 '16 at 11:45
  • Remove the leading comma: `Set xlApp = CreateObject("Excel.Application")` – Alex K. Sep 20 '16 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 Sep 20 '16 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 Sep 20 '16 at 12:07

1 Answers1

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"
REdim.Learning
  • 655
  • 2
  • 14
  • 32