refresh linked query

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?
 
J

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.
 
J

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
 
O

ozgrid.com

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top