refresh linked query

  • Thread starter Thread starter joemeshuggah
  • Start date Start date
J

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?
 
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.
 
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
 
Cool. Why not set the QueryTable Properties to automatically Refresh every x
minutes or upon open?
 
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
 
Back
Top