Automation/macro

Jonathan Cooper · Apr 22, 2004

I have a spreadsheet setup that automatically dowloads information from our AS/400 using MSQuery. Once the data is DONE being downloaded (which could take 3 minutes), I would like the pivot table on the next worksheet to be refreshed.

Any ideas

first sheet name is: DAT
second sheet name is: PIVOT REPOR

The only code I have is

Sub Aut

pen(
ActiveWorkbook.RefreshAl
End Sub

Frank Kabel · Apr 22, 2004

Hi
try the following:

Sub Aut

pen()
Dim ws As Worksheet
Dim pt As PivotTable
ActiveWorkbook.RefreshAllÂ´

On Error Resume Next
set ws = worksheets("Pivot Report")
For Each pt In ws.PivotTables
pt.RefreshTable
NextÂ´
on error goto 0
End Sub

jeff · Apr 22, 2004

I'm sure one of the MVPs has the exact answer, but
perhaps you could put the same refresh line in
the Worksheet_SelectionChange ???

Just a thought..

jeff

-----Original Message-----
I have a spreadsheet setup that automatically dowloads

information from our AS/400 using MSQuery. Once the data
is DONE being downloaded (which could take 3 minutes), I
would like the pivot table on the next worksheet to be
refreshed.

Jonathan Cooper · Apr 22, 2004

Didn't work. The pivot table refreshed before all the data was downloaded

----- Frank Kabel wrote: ----

H
try the following

Sub Aut

pen(
Dim ws As Workshee
Dim pt As PivotTabl
ActiveWorkbook.RefreshAllÂ

On Error Resume Nex
set ws = worksheets("Pivot Report"
For Each pt In ws.PivotTable
pt.RefreshTabl
NextÂ
on error goto
End Su

-
Regard
Frank Kabe
Frankfurt, German

Jonathan Cooper wrote

Frank Kabel · Apr 22, 2004

Hi
you may place an
application.wait
statement between both parts of your code our you put some code in the
workbook_sheetchange event to update the pivot table if you activate
this sheet