Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save and Close Excel using OnTime
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save and Close Excel using OnTime
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel creating TMP with each save but not deleting on close | Excel Discussion (Misc queries) | |||
How do you force excel to save on close | Excel Discussion (Misc queries) | |||
Let me save/close EXCEL if a formula I put in has an error in it | Setting up and Configuration of Excel | |||
Close excel do not save | Excel Worksheet Functions | |||
File close excel does not ask me to save changes | Excel Discussion (Misc queries) |