refresh linked query

[joemeshuggah]

i have a pivot table in an excel sheet that is linked to an access database.
i would like to program a macro to refresh the pivot table. i thought the
following would work, but it does not:

FinalRowFRONT = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("a" & FinalRowFRONT).Select
Selection.QueryTable.Refresh BackgroundQuery:=False

how do i get the pivot table to refresh?

 

[ozgrid.com]

The macro recorder is you friend

 

[joemeshuggah]

i actually used the recorder to get the snippet "Selection.QueryTable.Refresh
BackgroundQuery:=False"...the only problem is i have a few different queries
to refresh that have variable starting points, which is why i used the
finalrow variable. the refresh only works for the range where the macro is
recorded. when i try using the variable range, i get an error.

 

[joemeshuggah]

pulled this from your main site...going to give this a try first thing monday
morning...cant wait!

Dim ws As Worksheet
Dim qs As QueryTable
For Each ws In Worksheets
For Each qs In ws.QueryTables
qs.Refresh (False)
Next qs
Next ws

 

[ozgrid.com]

Cool. Why not set the QueryTable Properties to automatically Refresh every x
minutes or upon open?

 

[AB]

Do you want to refresh a QueryTable or a PivotTable? I'm not sure you
can refresh PivotTable by refreshing QueryTable.

For PivtTables i usually use something like this:
Workbooks(1).PivotCaches(1).Refresh
or
Worksheets(1).PivotTables(1).PivotCache.Refresh