Unanswered: Access Button to Open Excel File and run its Macros
Hello. I’m looking for some help. I’ve been slowly developing this access database for my department at work, and need some help. I currently have it set up so that when I press a button, it will ask for a date range, and export a Query to Excel. I’ve then gone and written a Macro in Excel that will convert the information in to a Pivot Table to make it easier to read. Here’s the problem.
Currently, I need to run the Query, then open the file that has the Macros, then run the macro. Ultimately, I would like to be able to just press the button in Access, enter my date range, and have it not only open the new file in Excel, but also open the file that has the macros, and if possible automatically run the Macro (Which is done by Ctrl + K.) I did some searching online and made an attempt at doing so, but it doesn’t seem to work. I won’t bother you with the code that opens the new excel file, because that works just fine. This is what I have to open the Macro File:
Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = False
It seems to open ok, but when I try to run the macro, its acting like its trying to add a hyperlink in, not run the macro. Any thoughts on what I can do to get it to open the macro file properly, and even extra help to automatically run the macro? Any help would be appreciated!
I figured out I could just add the Pivot Table Code to the Access Code, thus going around my problem. However, it has created a new program.
The code worked just fine as the Excel Macro. However, when I run it out of Access, I get an error message on the same line of code - Every Other Time.
The message I get is: “Run Time Error ‘1004’: Method worksheets’ of object _Global’ failed”
The trouble seems to be with the code where it selects the range for the Pivot Table. The range is dynamic (can change depending on the date range it pulls from, so my code looks, as follows:
Dim LR As Long
Dim LC As Long
LR = .Rows.Count
LC = .Columns.Count
exWB.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R" & LR & "C" & LC).CreatePivotTable TableDestination:="", TableName:= _
The specific piece I get the error message on is: