Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2015

    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
        oApp.Workbooks.Open ("U:\FilePath\PivotTableMacros.xlsb")
    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!

    PS – I am running Office 2007 on Windows 7.

  2. #2
    Join Date
    Feb 2015

    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
        With Worksheets("Sheet1").Range("A1").CurrentRegion              
            LR = .Rows.Count
            LC = .Columns.Count
        End With
        exWB.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "Sheet1!R1C1:R" & LR & "C" & LC).CreatePivotTable TableDestination:="", TableName:= _
            "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    The specific piece I get the error message on is:

    With Worksheets("Sheet1").Range("A1").CurrentRegion

    Any thoughts?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts