1

I've found a few threads that are similar to what I'm trying to do, but I need to call a macro stored in an XLAM add-in.

I have an XLAM which contains custom add-in functions available to any Excel file. I am trying to call a macro within that XLAM. I am using the Workbooks.Add rather than opening a specific file. Below is the code I'm using:

Option Explicit

RunMacro

Sub RunMacro()

  Dim xlApp
  Dim xlBook

  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Add
  xlApp.Run "C:\ATPBGC97\atpbgc2007.xlam\ExportModules"
  xlApp.DisplayAlerts = False
  xlBook.Saved = False
  xlApp.ActiveWorkbook.Close

  Set xlBook = Nothing
  Set xlApp = Nothing

End Sub

I used the thread Run Excel Macro from Outside Excel Using VBScript From Command Line to get this far. When I run the script, I get a 'Cannot run the macro' error. I'm not exactly sure what the syntax should be in my case or if I should just open a dummy file to gain access to the XLAM.

Community
  • 1
  • 1
user1769597
  • 55
  • 1
  • 1
  • 4
  • Assuming the addin is loaded by default, just use `xlApp.Run "ExportModules"` – Comintern Feb 22 '17 at 14:58
  • @Comintern - yes, the add-in is loaded by default. I tried that and get the error "Cannot run the macro 'ExportModules'. The macro may not be available in this workbook or all macros may be disabled." – user1769597 Feb 22 '17 at 15:07
  • Try replacing all of the code below the line `Set xlBook = xlApp.Workbooks.Add` with `xlApp.Visible = True`. When the Excel instance loads up, check to see if the addin is loaded. If not, you might just need to load it manually. – Comintern Feb 22 '17 at 15:11
  • @Comintern - yeah, you're right - it doesn't load them when Excel opens this way. When I open a blank worksheet, it does load the addins. I guess I'll have to find another way, as loading it manually defeats the purpose of this script. Thanks – user1769597 Feb 22 '17 at 15:46
  • You might want to try `xlApp.Addins("atpbgc2007").IsLoaded = True` to see if it will load dynamically at runtime. I've had various luck in calling that via VB though. – Comintern Feb 22 '17 at 15:51
  • Did u ever resolve this? I'm trying to do the same thing and no luck, works fine if I call `xlsm` and the macro inside that file, but not external `xlam` add-in. – FreeSoftwareServers Jan 09 '20 at 23:31

1 Answers1

0
xlApp.open "myfile.xlsm"
xlApp.open "myaddin.xlam"
xlApp.run "mymacro"

Worked for me. Make sure your macro has a stop to be able to edit it.

user3144385
  • 21
  • 1