Error when triying to refresh pivot table within MS Access form
Posted on 2004-10-06
I am trying to issue out a refresh to a pivot table in Excel from Access.
I have been looking over this wonderful site and found the following code:
Dim xl As Excel.Application
Set xl = New Excel.Application
Me!PivotTable.Verb = acOLEVerbOpen
Me!PivotTable.Action = acOLEActivate
The posting indicated that this works. I have put the code in the Open event of the PivotTable form and it opens the PivotTable in Excel directly however I get the error "Object variable or With block variable not set" on the statment "xl.ActiveSheet.PivotTables("PivotTable1").RefreshTable".
In debug I can see that the Excel.Application statement in the set shows the same value.
I have registered (I think, I'm still pretty new to the Access coding stuff) the Excel Class library.