graham,

According to VBE Help! you should be able to specify whether the pivot table
refreshes asynchronously or synchronously using the BackgroundQuery property
of the PivotCache object (the PivotCache object is a property of the
PivotTable).

Thus, you should be able to do something like:

Dim PT1 As PivotTable
Dim PC1 As PivotCache

Set PT1 = Activesheet.PivotTables("PivotTable1")
Set PC1 = PT1.PivotCache
PC1.BackgroundQuery = False

This sets the PivotCache to refresh synchronously (i.e., NOT in the
background), which means that VB won't execute the next command until the
refresh is complete. This would be the preferred solution.

I tried to duplicate your problem, but the BackgroundQuery property for my
PivotCache object is set to False and I get an error when I try to change
it. Perhaps it will cooperate for you if you try to set it to False (it must
presently be set to True for your program to act the way it does). If this
strategy doesn't work, maybe someone can explain why.

You can also try using the Wait method (a sledgehammer approach), eg:

Application.Wait (Now + 3.5 / 1440)

This statement will make Excel wait 3.5 minutes before proceeding to the
next command. According to VBE Help! only certain types of Excel operations
will continue while the application waits, including recalculations, so this
may work for you.

HTH,
Shockley








"graham" wrote in message
...
Complete newbie to VBA here, thanks for your patience...

I have an auto_run macro that calls another excel sheet, refreshes a pivot
table (usually takes about 2-3 minutes) then try's to save and close excel
completely. The problem is that it try's to save/close before the pivot
table is finished refreshing. After several posts, much research and quite
a bit less hair, It seems like an OnTime app to tell excell to wait 3
minutes before trying to save and 3.5 minutes to close would work.

Except that I have no clue how to do it!

Here's my code so far:

Sub Auto_Open()
'
' Auto_Open Macro
' Macro recorded 6/23/2004 by Me
'

'
ChDir "T:\Data"
Workbooks.Open Filename:= _
"T:\Data\sample.xls"
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
ActiveWorkbook.Save
ActiveWindow.Close
Set excelApp = GetObject(, "Excel.Application")
excelApp.Quit
End Sub


How do I write the Ontime app to wait 3 minutes (from opening the
workbook) before saving sample.xls and another 30 seconds or so to close
excel???

THANK YOU, THANK YOU, THANK YOU

sweet, thank you very much. I've been banging my hand against the computer for over a week. I didn't realize the impact of a background query (usually the default for pivot tables I thought). You can turn this off under table options in the pt.

thanks again

"shockley" wrote:

graham,

According to VBE Help! you should be able to specify whether the pivot table
refreshes asynchronously or synchronously using the BackgroundQuery property
of the PivotCache object (the PivotCache object is a property of the
PivotTable).

Thus, you should be able to do something like:

Dim PT1 As PivotTable
Dim PC1 As PivotCache

Set PT1 = Activesheet.PivotTables("PivotTable1")
Set PC1 = PT1.PivotCache
PC1.BackgroundQuery = False

This sets the PivotCache to refresh synchronously (i.e., NOT in the
background), which means that VB won't execute the next command until the
refresh is complete. This would be the preferred solution.

I tried to duplicate your problem, but the BackgroundQuery property for my
PivotCache object is set to False and I get an error when I try to change
it. Perhaps it will cooperate for you if you try to set it to False (it must
presently be set to True for your program to act the way it does). If this
strategy doesn't work, maybe someone can explain why.

You can also try using the Wait method (a sledgehammer approach), eg:

Application.Wait (Now + 3.5 / 1440)

This statement will make Excel wait 3.5 minutes before proceeding to the
next command. According to VBE Help! only certain types of Excel operations
will continue while the application waits, including recalculations, so this
may work for you.

HTH,
Shockley








"graham" wrote in message
...
Complete newbie to VBA here, thanks for your patience...

I have an auto_run macro that calls another excel sheet, refreshes a pivot
table (usually takes about 2-3 minutes) then try's to save and close excel
completely. The problem is that it try's to save/close before the pivot
table is finished refreshing. After several posts, much research and quite
a bit less hair, It seems like an OnTime app to tell excell to wait 3
minutes before trying to save and 3.5 minutes to close would work.

Except that I have no clue how to do it!

Here's my code so far:

Sub Auto_Open()
'
' Auto_Open Macro
' Macro recorded 6/23/2004 by Me
'

'
ChDir "T:\Data"
Workbooks.Open Filename:= _
"T:\Data\sample.xls"
ActiveSheet.PivotTables("PivotTable1").RefreshTabl e
ActiveWorkbook.Save
ActiveWindow.Close
Set excelApp = GetObject(, "Excel.Application")
excelApp.Quit
End Sub


How do I write the Ontime app to wait 3 minutes (from opening the
workbook) before saving sample.xls and another 30 seconds or so to close
excel???

THANK YOU, THANK YOU, THANK YOU