• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Saving an Excel Workbook is SLOOOW - even with all VBA disabled

A

alexsheehan

Member
Hi


I have an Excel Workbook which is working perfectly, however upon saving, it takes an AAAAGE to save - seriously, I could grow a full-on ZZ-Top style beard in the time it takes to save.


My file is only around 500kb in size, and I have temporarily disabled all VBA to try and troubleshoot the problem. I have a few lookup formulaes but nothing too drastic.


Does anyone have any tips on what may cause slow saving? Do I need to carry out such drastic measures as copying out all worksheet tabs/code to a completely new Workbook?


I have tried saving the file to a different location/filename, taken all code out, I have even resorted to copying and pasting everything as values but the file still takes longer than a microscopic organism takes to traverse across the surface of a snale, who's riding atop a slug travelling the wrong way across a sloth's belly.


Any help or advice would be greatly appreciated.


Cheers


Alex
 
make sure you don't have excessive blank rows or columns on all worksheets.


You can tell by the look of the vertical and horizontal scroll bars. If you scroll, say, the vertical bar on the right all the way until the bottom of the indicator touches lower right of the screen, and there is no data. DELETE all the rows. I have seen a case where a 36MB file trimming its size down to 2MB because of this simple oversight.
 
Hi ,


When you press CTRL END , it should take you to the last used cell in the worksheet. See whether this correlates with what it should be. If not delete all the excess rows and columns.


Narayan
 
Thanks guys,


Yes I have made sure that the "data area" is restricted to only the area being used (no excessive blank rows/columns/cells). As a matter of fact, I even tried saving the file under a different file name and COMPLETELY deleting all data/formulaes/VBA/Conditional formats/formatting and it STILL refuses to save any faster. The spreadsheet now has no data whatsoever, no formatting of any kind - completely blank and still takes ages.


Other spreadsheets save fine by the way, I have tried copying out all data to a new spreadsheet (create new workbook, create new worksheet, copy data from old workbook/worksheet & paste entire data into new workbook/worksheet.) but this causes causes the new workbook to follow suit.


I have spent an awful long time trying to develop this spreadsheet and although it functions perfectly, it is unuseable because any minor change takes about 30 mins to save.
 
When you copy to the new workbook, try copying values only, test saving; then copy formats, test saving; then copy formulas, test saving; then go ahead with the vba code.


The paste-specials, even if you do all of them, will hopefully eliminate the issue.


This is an odd one.


Another idea is saving it in a different format, such as xls or xlsb if it's currently xlsx, or vice-versa.


Asa
 
Hi ,


You might like to look at the following link :


http://datapigtechnologies.com/blog/index.php/hack-into-a-protected-excel-2007-sheet/


Even though your workbook may not be protected , you can follow the procedure given in the link ; rename your .xlsx file to .zip , and open it with WinZip or any other equivalent software.


Even though the workbook itself may be small in size , some of the XML files which it consists of , can run into sizes of even 100 MB. Looking at it this way may tell something about which of the workbook component files is contributing to the slowness in saving.


I am sure you don't have any problems in opening the workbook ?


Narayan
 
OK, I've tried all of hte above and I think I've tracked it down to one particular worksheet "Database2", but I don't quite know where to go from here as there is nothing particularly strange about it or out of the ordinary as far as I can tell.


Would someone be able to look at it for me by any chance? If so, I will save it to SkyDrive and in that case I will need your email addresses to add to the "Permissions" list. I think I just need a fresh set of eyes to be honest as I can't fathom it myself.


I know this is a bit cheeky of me, but I cannot for the life of me see what is wrong - ok there are a few vlookups, but nothing drastic, I also have a few hyperlinks and conditional formats in there etc. but as I stated above, even when resorting to the drastic measures of copying and pasting everything as values it still saves slowly.


Thanks very much in advance.
 
Hi ,


My email address is narayank1026@gmail.com


If your problem is still persisting , I can try to go through your worksheet.


Narayan
 
Thanks Narayan


I have sent a link to your email. Thanks for taking a look at it for me - as stated above, I think the main culprit is "Database2" Worksheet.


Let me know how you get on.


Alex
 
Hi Alex ,


I don't see any problem ! Saving on my PC ( I tried both a Save and a Save As ) , takes only a few seconds !


Narayan
 
Thanks Narayan


Problem solved!


After many, many, M A N Y hours of picking at it I have copied it all into a new workbook, copying and pasting in section by section (copying the entire data range seemed to still throw up an error). I still don't really know what the cause was as I had no extra cells/columns/rows or any erroneous formulaes and as you say, on your system it saved really quickly. Hmmm...


Anyway, it's back to it's original save time now, saved under the original name and location and it saves in seconds now.


Thanks very much for spending the time to help me out guys.


Best regards


Alex
 
Hi Alex ... Based on Narayan's feedback, I'm thinking that there could be something else on your system causing the problem.


http://support.microsoft.com/kb/329820/


Try disabling your antivirus and or any Office-file plugin or scanning features.


Apparently mapped network drives for nonexistent or unreachable network locations can cause this issue; you can try disconnecting/removing such drive mappings.


More suggestions at:

http://joshuabrauer.com/save-as_slow_microsoft_office

http://endorkins.com/2009/07/13/slow-excel-spreadsheet-try-this/


Asa
 
You must log in or register to reply here.
Back
Top