Prev: Accessing the Data Connection Strings and Command Text (SQL) i
Next: Splitting a string into columns
From: joemeshuggah on 30 Apr 2010 18:02
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?
From: ozgrid.com on 30 Apr 2010 20:23
The macro recorder is you friend :)



--
Regards
Dave Hawley
www.ozgrid.com
"joemeshuggah" <joemeshuggah(a)discussions.microsoft.com> wrote in message
news:731FB1FB-BEA3-4E6B-AD4A-9BD40B44CD81(a)microsoft.com...
>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?

From: joemeshuggah on 30 Apr 2010 23:54
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.

"ozgrid.com" wrote:

> The macro recorder is you friend :)
>
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "joemeshuggah" <joemeshuggah(a)discussions.microsoft.com> wrote in message
> news:731FB1FB-BEA3-4E6B-AD4A-9BD40B44CD81(a)microsoft.com...
> >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?
>
From: joemeshuggah on 1 May 2010 00:11
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


"joemeshuggah" wrote:

> 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.
>
> "ozgrid.com" wrote:
>
> > The macro recorder is you friend :)
> >
> >
> >
> > --
> > Regards
> > Dave Hawley
> > www.ozgrid.com
> > "joemeshuggah" <joemeshuggah(a)discussions.microsoft.com> wrote in message
> > news:731FB1FB-BEA3-4E6B-AD4A-9BD40B44CD81(a)microsoft.com...
> > >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?
> >
From: ozgrid.com on 1 May 2010 02:32
Cool. Why not set the QueryTable Properties to automatically Refresh every x
minutes or upon open?




--
Regards
Dave Hawley
www.ozgrid.com
"joemeshuggah" <joemeshuggah(a)discussions.microsoft.com> wrote in message
news:31C41AF9-DD10-4F
17-A8F9-F4D77F37CB70(a)microsoft.com...
> 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
>
>
> "joemeshuggah" wrote:
>
>> 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.
>>
>> "ozgrid.com" wrote:
>>
>> > The macro recorder is you friend :)
>> >
>> >
>> >
>> > --
>> > Regards
>> > Dave Hawley
>> > www.ozgrid.com
>> > "joemeshuggah" <joemeshuggah(a)discussions.microsoft.com> wrote in
>> > message
>> > news:731FB1FB-BEA3-4E6B-AD4A-9BD40B44CD81(a)microsoft.com...
>> > >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?
>> >

 |  Next  |  Last
Pages: 1 2
Prev: Accessing the Data Connection Strings and Command Text (SQL) i
Next: Splitting a string into columns