I'm trying to Auto_run a macro in a different workbook that opens an existing file, refreshes the pivot table, saves, then closes excel completely. My only problem is that my code trys to close excel before the refresh is complete. How can I tell it to finish the refresh operation before continuing?

THANKS!!!

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

You might try

DoEvents

before ActiveWorkbook.Save

"graham" wrote:

I'm trying to Auto_run a macro in a different workbook that opens an existing file, refreshes the pivot table, saves, then closes excel completely. My only problem is that my code trys to close excel before the refresh is complete. How can I tell it to finish the refresh operation before continuing?

THANKS!!!

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

OR:

while application.calculationversion < activeworkbook.calculationversion


"AA2e72E" wrote:

You might try

DoEvents

before ActiveWorkbook.Save

"graham" wrote:

I'm trying to Auto_run a macro in a different workbook that opens an existing file, refreshes the pivot table, saves, then closes excel completely. My only problem is that my code trys to close excel before the refresh is complete. How can I tell it to finish the refresh operation before continuing?

THANKS!!!

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

'While' is the opening statement of a 'While-Wend' loop like 'Do' is the
opening statement of a 'Do-Loop' loop. AA2e72E assumes you'll know that and
will put the Wend in the right place. That having been said, I don't
understand how the CalculationVersion condition will help.

I don't believe there is a clear answer to your dilemma. VBA sometimes just
doesn't execute code in the order you might think it should, or the Refresh
might be effectively asynchronous. The DoEvents suggestion was a good one,
but if it didn't work, you'll have to try other things to coerce VBA to wait
for the Refresh to complete. Try putting the Quit in a separate Sub and call
that Sub where you now invoke the Quit. Or try checking something that
indicates the referesh finished before invoking the quit. You could also use
a timer and call the Quit after a few second delay estimated to be
sufficient for the Refresh to complete.See the OnTime method.

"graham" wrote in message
...
Thanks for the ideas,

DoEvents didn't seem to stop the code from continuing,

the second option is giving me a compile error stating:

While without Wend

???
THANKS!

"AA2e72E" wrote:

OR:

while application.calculationversion <
activeworkbook.calculationversion


"AA2e72E" wrote:

You might try

DoEvents

before ActiveWorkbook.Save

"graham" wrote:

I'm trying to Auto_run a macro in a different workbook that opens an
existing file, refreshes the pivot table, saves, then closes excel
completely. My only problem is that my code trys to close excel before the
refresh is complete. How can I tell it to finish the refresh operation
before continuing?

THANKS!!!

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