[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..
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..
Last edited: