Run-time 1004 Error When Trying To Call Excel Macro (1 Viewer)

[SOLVED] Run-time 1004 Error When Trying To Call Excel Macro

Hi!

I've been rummaging about online and trying to solve this for hours now and I've finally given up in the hope someone more endowed with brain cells can help me out..

I'm working in Access, and I have a few reports that I run and that I've been copying and pasting into Excel and then doing some more work on. It occurred to me that I could write a macro to do all this for me, so I've made one in Access, and one in Excel to do the copying sheets etc, but the problem is in passing from one to the other. My Access module runs fine (after a lot of debugging) as does my Excel one, but I want Access to call the Excel macro at the end and hand over to it to finish the work. I put this part into a separate sub so I could test it without running the reports etc every time and this is my code:

Public Sub RunExcelMacro()

Dim objExcel As Object
Dim objWorkBook As Object

Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("H:\IT Department\General\Reporting\Season Ticket Analysis\Season Ticket Analysis - Data.xlsm")
objExcel.Visible = True
'objExcel.DisplayAlerts = False

objExcel.Run "SeasonTicketAnalysis"

End Sub

But I get this error.

Run-time error '1004': Cannot run the macro 'SeasonTicketAnalysis'. The macro may not be available in this workbook or all macros may be disabled.

Does anyone have any idea what the issue might be? It seems like an (unusually) helpful error message but I've checked that access to the Excel VB module is trusted (it is), that the name of the macro is spelled exactly as above (it is) and that the macro runs fine in Excel (it does), and that macro security isn't set to high (its set to the not recommended very low run all macros level), and after that I've run out of ideas. The macro is within the xlsm workbook this code opens, not in Personal.

Thank you very much in advance for any help..

The way the error message reads, it does sound like it could be a security issue and that all macros have been disabled.
However all is not lost, why can't you just run the macro code from Access, just copy all the code from the Excel macro into an Access function and call it from your Sub above

David

Thanks David, I didn't know it was the same syntax and would work in either application, I actually just managed to get it working calling the Excel macro from Access, when I went to run the macro in Excel I noticed it refers to the macro as 'ThisWorkbook.SeasonTicketAnalysis', not just 'SeasonTicketAnalysis', so I tried the full thing in the VBA code (objExcel.Run "ThisWorkbook.SeasonTicketAnalysis") and that's it working fine. Strange that that isn't a common problem, I couldn't find the answer online anywhere.

Thanks for the info though, very helpful for future!

The option to mark as solved isn't there for me, so I give up.