Automation/macro

[Guest]

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 Auto_Open(
ActiveWorkbook.RefreshAl
End Sub

 

[Frank Kabel]

Hi
try the following:

Sub Auto_Open()
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]

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.

 

[Guest]

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

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

H
try the following

Sub Auto_Open(
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]

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

 

[Guest]

Keeping in mind that I don't know anything about VBA and I'm just guessing, couldn't I use something like this example to tell Excel, 'when you are done downloading, refresh the pivot table.' It just seems to me that the completion of a download should be an EVENT that could trigger another action

Exampl
This example uses the Success argument to determine which section of code to run

Private Sub QueryTable_AfterRefresh(Success As Boolean
If Succes
' Query completed successfull
Els
' Query failed or was cancelle
End I
End Su



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

H
you may place a
application.wai
statement between both parts of your code our you put some code in th
workbook_sheetchange event to update the pivot table if you activat
this shee

-
Regard
Frank Kabe
Frankfurt, German


Jonathan Cooper wrote