File saves ok, SaveAs VERY slow
Hi All......
I have a 10MB Excel file that will "save" in about 30 seconds. As soon as I hit the save button, the Disk-activity light starts flashing. But, when I do a "Save-as" to just change the name, it takes a total of 6 minutes. It starts out with the Status bar saying "Calculating cells" but it stays at zero for over 3 minutes, then works it's way up slowly every 15 seconds or so and when it changes to "Saving, filename" the Disk-activity light starts to flash and it only takes the normal 30 seconds from that point....... I do have about 40 sheets, with LOTS of VLOOKUPS, but I don't understand the difference between the SAVE and the SAVEAS times...... Can anyone please explain, and hopefully tell me what I might can do to cut down the difference in time? TIA Vaya con Dios, Chuck, CABGx3 |
Hi CLR,
This is a cop-out but I rarely use 'Save as' myself. Why don't you just use Windows Explorer and copy the file, then make your changes in the copy. I'm can't think of a good reason for Save and Save as to give such different results. Cheers, Mark "CLR" wrote: Hi All...... I have a 10MB Excel file that will "save" in about 30 seconds. As soon as I hit the save button, the Disk-activity light starts flashing. But, when I do a "Save-as" to just change the name, it takes a total of 6 minutes. It starts out with the Status bar saying "Calculating cells" but it stays at zero for over 3 minutes, then works it's way up slowly every 15 seconds or so and when it changes to "Saving, filename" the Disk-activity light starts to flash and it only takes the normal 30 seconds from that point....... I do have about 40 sheets, with LOTS of VLOOKUPS, but I don't understand the difference between the SAVE and the SAVEAS times...... Can anyone please explain, and hopefully tell me what I might can do to cut down the difference in time? TIA Vaya con Dios, Chuck, CABGx3 |
Thanks anyway Mark, but I make many, many changes every day, and this
super-computer I'm provided with crashes or locks up 3-4 times every day.....I save frequently to keep from losing any more work than necessary. I don't just "save" because sometimes the save gets corrupted and then I lose both.......so I Save-as and change the name to Rev3 or 4 or whatever each time....... I appreciate your suggestion, but maybe someone else might can tell me what really causes the difference in the save-times, and what I might can do about it. Vaya con Dios, Chuck, CABGx3 "Mark Hone" wrote: Hi CLR, This is a cop-out but I rarely use 'Save as' myself. Why don't you just use Windows Explorer and copy the file, then make your changes in the copy. I'm can't think of a good reason for Save and Save as to give such different results. Cheers, Mark "CLR" wrote: Hi All...... I have a 10MB Excel file that will "save" in about 30 seconds. As soon as I hit the save button, the Disk-activity light starts flashing. But, when I do a "Save-as" to just change the name, it takes a total of 6 minutes. It starts out with the Status bar saying "Calculating cells" but it stays at zero for over 3 minutes, then works it's way up slowly every 15 seconds or so and when it changes to "Saving, filename" the Disk-activity light starts to flash and it only takes the normal 30 seconds from that point....... I do have about 40 sheets, with LOTS of VLOOKUPS, but I don't understand the difference between the SAVE and the SAVEAS times...... Can anyone please explain, and hopefully tell me what I might can do to cut down the difference in time? TIA Vaya con Dios, Chuck, CABGx3 |
CLR wrote:
Hi All...... I have a 10MB Excel file that will "save" in about 30 seconds. As soon as I hit the save button, the Disk-activity light starts flashing. But, when I do a "Save-as" to just change the name, it takes a total of 6 minutes. It starts out with the Status bar saying "Calculating cells" but it stays at zero for over 3 minutes, then works it's way up slowly every 15 seconds or so and when it changes to "Saving, filename" the Disk-activity light starts to flash and it only takes the normal 30 seconds from that point....... I do have about 40 sheets, with LOTS of VLOOKUPS, but I don't understand the difference between the SAVE and the SAVEAS times...... Can anyone please explain, and hopefully tell me what I might can do to cut down the difference in time? TIA Vaya con Dios, Chuck, CABGx3 As an experiment, have you tried to turn off auto-calculation and "Recalculate before save" before doing the SaveAs? It can make a dramatic difference sometimes. Also, are you saving over a network or directly in your local hard drive? I have some files of about 10MB in which I keep calculation set to manual to speed things up. I can edit things to my heart's content quickly, then push F9 to force a manual calculation when necessary. ToolsOptionsCalculationTab Bill |
that did the trick.......kind of aggravating to have to do that, but it did
solve the "save-time" problem. Thank you muchly kind Sir........ Vaya con Dios, Chuck, CABGx3 "Bill Martin -- (Remove NOSPAM from addre" wrote: CLR wrote: Hi All...... I have a 10MB Excel file that will "save" in about 30 seconds. As soon as I hit the save button, the Disk-activity light starts flashing. But, when I do a "Save-as" to just change the name, it takes a total of 6 minutes. It starts out with the Status bar saying "Calculating cells" but it stays at zero for over 3 minutes, then works it's way up slowly every 15 seconds or so and when it changes to "Saving, filename" the Disk-activity light starts to flash and it only takes the normal 30 seconds from that point....... I do have about 40 sheets, with LOTS of VLOOKUPS, but I don't understand the difference between the SAVE and the SAVEAS times...... Can anyone please explain, and hopefully tell me what I might can do to cut down the difference in time? TIA Vaya con Dios, Chuck, CABGx3 As an experiment, have you tried to turn off auto-calculation and "Recalculate before save" before doing the SaveAs? It can make a dramatic difference sometimes. Also, are you saving over a network or directly in your local hard drive? I have some files of about 10MB in which I keep calculation set to manual to speed things up. I can edit things to my heart's content quickly, then push F9 to force a manual calculation when necessary. ToolsOptionsCalculationTab Bill |
CLR wrote:
that did the trick.......kind of aggravating to have to do that, but it did solve the "save-time" problem. Thank you muchly kind Sir........ Vaya con Dios, Chuck, CABGx3 -------------------------- You're quite welcome. There's one other trick that I use which may or may not apply to your case. My large files are typically lots of sheets deep. Each sheet has a large block of data and then appended off to the right are several columns of involved, ugly calculations based on that data -- and some data from a couple of other sheets. Once it's calculated though, it never really changes again for that sheet. In this case, once the sheet is calculated I copy the sheet and then paste it back onto itself using PasteSpecialValues. This gets rid of a thousand long formulas and replaces them with the simple numbers that they evaluated to. Only the most recent few sheets are left in their full formulaic glory. Doing this makes the file much smaller to store, and much faster to calculate if a full recalc is done. Good luck... Bill |
Thank you again Bill......
I have about 40 sheets each with about 300 VLOOKUP formulas on them. They must calculate to be current, then need not re-calculate unless there is a need to re-evaluate them.....similar to what you say. I can clear them all and re-load the formulas programmatically when updating, so will probably take this new advice also and just delete the formulas when not in use and load them only when needed......that ought to really slim things down..... I appreciate you taking the extra time to mention this.....thanks again. Vaya con Dios, Chuck, CABGx3 "Bill Martin -- (Remove NOSPAM from addre" wrote: CLR wrote: that did the trick.......kind of aggravating to have to do that, but it did solve the "save-time" problem. Thank you muchly kind Sir........ Vaya con Dios, Chuck, CABGx3 -------------------------- You're quite welcome. There's one other trick that I use which may or may not apply to your case. My large files are typically lots of sheets deep. Each sheet has a large block of data and then appended off to the right are several columns of involved, ugly calculations based on that data -- and some data from a couple of other sheets. Once it's calculated though, it never really changes again for that sheet. In this case, once the sheet is calculated I copy the sheet and then paste it back onto itself using PasteSpecialValues. This gets rid of a thousand long formulas and replaces them with the simple numbers that they evaluated to. Only the most recent few sheets are left in their full formulaic glory. Doing this makes the file much smaller to store, and much faster to calculate if a full recalc is done. Good luck... Bill |
All times are GMT +1. The time now is 01:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com