automating external linked pivot table

[Guest]

I have a pivot table that is linked to an access query for the data. I'm
attempting to get a macro that will refresh the data after some data in the
excel sheet is changed. The change is linked into a table in ms access and
the query uses that table to calculate. If I just hit refresh on the pivot
table, it works fine, but I can't seem to get a macro to work - i'm very new
to vba programming.

 

[Debra Dalgleish]

You can use event code to refresh the pivot table. For example,
right-click on the sheet tab of the sheet where you'll make the change,
and choose View Code.
Where the cursor is flashing, paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot").PivotTables(1).PivotCache.Refresh
End Sub

Change the sheet name to the pivot table sheet name in your workbook.
If you want the pivot table to refresh only when a specific cell is
changed, you could test for that in the code. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$2" Then
Worksheets("Pivot").PivotTables(1).PivotCache.Refresh
End If
End Sub

 

[Guest]

Will this work for the pivot cache coming in from an access query? I need
the query to update the data for the pivot table - When I've tried the below
type of code, I get the following run time error (1004) [Microsoft][ODBC
Microsoft Access Driver]Connection for viewing your linked microsoft excel
worksheet was lost.

When choose tools/references in visual basic - I get a error access system
registery.

I'm starting to link there is something wrong inside my microsoft not being
able to access something it needs.