I have once again run into a major probem in Excel 2010 related to doing major surgery on a large file and ending up with it being unwilling to Save and requiring me to perform some serious “sleight-of-hand” to save my efforts into a new File that WOULD save successfully. I am curious if anyone else has had the same problem (??).
1. BIG original spreadsheet from XL2003 (probably started in XL 5 time-frame – seriously).
2. Lotsa Worksheets
3. Has lotsa Conditional formats that I mostly had to remove to save the file earlier in the process.
4. Uses Many Tables and Structured References in a big (and very cool) way. Lotsa tables linking back and forth to each other (also in cool ways). This is all relatively new to the file and may or may not be releated to the problem.
5. I deleted several large and no longer necessary Worksheets from the file and deleted all the REF’ed out refernces that resulted.
6. The file gave me an error to the effect that it was corrupted and would need to be repaired. Woud I like Excel to repair it? Then after attempting to repair it Excek said “Document Not Saved” (Tough s**t in footnotes).
To save the work I:
1. Created a new file
2. Copied each functional remaining worksheet over the the new file one at a a time.
3. Went through the new file and removed all Linked references.
Then saved it as an XLSX file and THEN opened it again, copied the code over, compiled the code, and saved it as an XLSM file.
Now the file works fine and saves fine too.
This is not the first 2010 file I’ve had this kind of problem with and it seems to relate to adding 2010 capabilities to file that started life in the pre-2007 era.
This is not a big downer on the new capabilities of Excel, just a warning to the wise that they should be ready for this type of thing in their conversions to come and they should work on their skill in this technique. They should also save a lot and use incrementally numbered files as much as practical so they can step backwards if necessary.
Hope all had a good Holiday Season and hope WE all have an “Excel”- lent New year 🙂 (sorry about that one).