asked on

Auto_Open Excel macro in an Access DB Pivot Table form

I have two similar access databases. The first, I have successfully gotten a macro to run automatically when opened to:

1) Maximize Excel
2) Maximize the worksheet
3) Select a cell in the pivot table
4) Refresh the pivot table's data

When I copy the macro into the second database's pivot table, it does not run when the pivot table is opened? Any ideas why? If I copy the form from the first db into the second, it does successfully start the macro's step 1, 2, and 3...but with the wrong tables, it does not refresh.

Thanks,
Don

ASKER CERTIFIED SOLUTION

tgraffham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

macrodev

ASKER

I think you got something there Tim...

I totally forgot to pay attention to whether or not I pasted the code in the module or the sheet. Thanks SO MUCH for jogging my memory.

Here's what the code was (pasted in Module1)

Sub Auto_Open()
'
' Auto_OPEN Macro
' Macro recorded 3/11/03 by DStimson
'

'Maximize Screen
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized

Application.Goto Reference:="EastSTART"
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Application.Goto Reference:="EastEND"
End Sub

------------------------------------------
Sub Auto_Close()

'Goto EastEND
Application.Goto Reference:="EastEND"
Application.WindowState = xlNormal
ActiveWorkbook.Save

End Sub

BUT...in looking, I found that I wrote this code in the "ThisWorkbook" Microsoft Excel Objects above the modules.

Private Sub Workbook_Open()

'Refresh data
'Application.OnTime Now + TimeValue("00:00:05"), "RunAutoOpen"
Application.OnTime Now + TimeValue("00:00:03"), "Auto_Open"

End Sub

---------------------------------------
Private Sub Workbook_Close()

'Goto EastEND
Application.Goto Reference:="EastEND"

End Sub

macrodev

ASKER

Thanks for the "where the code is" ...that hit the mark.

It now works.