Errors While Trying to Run an Excel Macro from Access

[drhayes]

I've been struggling with this problem for hours.



I've used transfer spreadshet to create the Test.xlsx file which I want to run a macro on. The Macro is called ShortFinishedAA. Every time I run it it says that "Cannot run the macro Test.xls!ShortFinishedAA the macro may not be available in this workbook or all macros may be disabled." When I go to the already opened excel file I notice that if you manually try to run the macro you find that there aren't any. Essentially, I think the Personal macro workbook I have Isn't loading even though it loads every other time I open an excel file.

Is there a way to load the personal macro workbook in this code? And then run the macro? Or is there a better way to do all of this?

strFile = "Test.xlsx"
strMacro = "ShortFinishedAA"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\Users\Danny\Desktop\CCFP\Client Accounts Data\Asset Allocations Daily Data\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing

End Function

[Rawb]

Try using the RunMacro Event instead of bang notation:

Replace:

xls.Run strFile & "!" & strMacro

With:

xls.RunMacro strMacro, ,