VBA Not Running in Exported Pivot Table

[Guest]

Using MS Access I've created a Piviot Table imbedded into an Access Form. I'm
opening a Pivot Table for editing in Excel my following code won't run:

Sub mcrRefreshPvt()
'
' mcrRefreshPvt Macro
' Keyboard Shortcut: Ctrl+r
'
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

However, if I follow the Debug prompt & then clik on "ThisWorkbook" I am
able to run my code & refresh the Pivot table. How do I get this code to
automatically run when I open the table in excel?

 

[Dick Kusleika]

Using MS Access I've created a Piviot Table imbedded into an Access Form. I'm
opening a Pivot Table for editing in Excel my following code won't run:

Sub mcrRefreshPvt()
'
' mcrRefreshPvt Macro
' Keyboard Shortcut: Ctrl+r
'
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

However, if I follow the Debug prompt & then clik on "ThisWorkbook" I am
able to run my code & refresh the Pivot table. How do I get this code to
automatically run when I open the table in excel?

What error do you get? It almost sounds like what you consider to be
the ActiveSheet and what Excel does are different. That doesn't
really make sense, but that's what it sounds like. Where is the code?
Is it in the workbook with the pivot table?

 

[Guest]

The code is in the workbook with the Pivot Table. The Pivot Table is embedded
in my MS Access Form as an "OLE".

However, I crated it again using the Record Macro function & got the
following that works:

Sub mcrRefreshPivot2()
'
' mcrRefreshPivot2 Macro
'
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

In this case "Pivotcache" is omitted. I don't pretend to understand this but
if you could tell me how to make it run upon opening the pivot table I'd be
very greatfull.

David