Macro not found when trying to run an Excel sub with VBS

Question

I've found the following code online to try an automate an Excel macro.

 ' Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application") 


Dim strSaveDefaultPath
Dim strPath
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath

' Open the Workbook specified on the command-line 
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = strPath & "\Excel Report Creator.xlsm"

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB,0, true)

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "'" & strPath & "\Excel Report Creator" & "!ReferenceSheet.CommandButton1_Click"

on error resume next 
    myExcelWorker.Run strMacroName
   if err.number <> 0 Then
   MsgBox "errerr: " & err.Description
   End If
   err.clear
on error goto 0 

oWorkBook.Save 

myExcelWorker.DefaultFilePath = strSaveDefaultPath

    ' Clean up and shut down
        Set oWorkBook = Nothing
        myExcelWorker.Quit
        Set myExcelWorker = Nothing
        Set WshShell = Nothing

When ran I receive the following error.

The macro may not be available in this workbook or all macros may be disabled.

My spread sheet is called Excel Report Creator.xlsm, the sheet is called ReferenceSheet, and the sub is CommandButton1_Click. Am I missing anything here? Possibly a setting within Excel?

Thanks, Bill

I would guess that your document is not trusted or in a trusted location, so when you open it in VBS, Excel won't allow the macros to run without a user clicking on the yellow Enable macros message bar. Go into the Trust Center in Excel and set <path-to-"Excel Report Creator.xlsm" to be a trusted location & you should be good to go. — FreeMan
– FreeMan, Commented Apr 17, 2015 at 15:32
Thanks for the quick response, I added my excel file, but I'm still receiving the error. — Bill Tudor
– Bill Tudor, Commented Apr 17, 2015 at 15:43
When you directly open the file in Excel do you get the macro disabled warning? You may want to try adding the entire folder as a trusted location. If that doesn't help, I'm out of my league. — FreeMan
– FreeMan, Commented Apr 17, 2015 at 16:03

Tim Williams · Accepted Answer · 2015-04-17 16:32:25Z

2

When the workbook name has spaces you need to wrap it in single quotes. And you don't need the full path if the workbook is open:

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "'Excel Report Creator.xlsm'" & _
               "!ReferenceSheet.CommandButton1_Click"

on error resume next 
    myExcelWorker.Run strMacroName
   if err.number <> 0 Then
   MsgBox "errerr: " & err.Description
   End If
   err.clear
on error goto 0

And make sure the Sub you're calling is Public

answered Apr 17, 2015 at 16:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Bill Tudor Over a year ago

Worked great. Now I'm getting an error saying "Microsoft Excel: We can't save 'Excel Report Creator.xlsm' because the file is read-only." Guess I'll start another question. Thanks.

Tim Williams Over a year ago

You opened the file as read-only by passing true to the third parameter of the open() method. Don't do that if you want to save the file.

Collectives™ on Stack Overflow

Macro not found when trying to run an Excel sub with VBS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related