Large Excel File issues

[NWSanta]

Good Afternoon All,

So let me be the first to dust off my login, it's been quite a while since I've posted anything here. I just wanted to say that thank you for posting all of your great posts in here as I've got all sorts of great answers from this forum.

My turn now, my HR department have developed this 60 MB XLSM Spreadsheet and are having all sorts of gremlin/problems with it now. It was working fine up until about 3 months ago. It pulls data from a database and puts it into an SQLDATA sheet in the workbook. They then link to that data in different sheets.
They are using Excel 2010 on a Windows 7 Professional platform.

The first problem that's popped up is that when they delete or remove a row from the spreadsheet, it corrupts the spreadsheet and we are unable to get it working again without going to a backup.
Next problem is, some of the sheets have comments in them with mostly pictures in the comments fields. What they notice now is sometimes they will make a change and something happens in the spreadsheet and all of the comments disappear.

Anyways, I honestly haven't worked to get my knowledge up to snuff with their spreadsheet but, I'm sure it must be something in the sheet that's causing it to wig out.

Looking to you gurus out there to shed some light on the situation.
Thanks
Evan

 

[ShawnPCooke]

I have had that issue before as well with spreadsheets, usually large ones and only when the spreadsheet is in XML format. I believe that the issue stems from the conversion between XML and binary. In Excel 2007 and later, the .xlsx and .xlsm file types are not stored as Excel binaries, but rather as xml. When you open a file, Excel converts the xml to binary, and when you save, it converts it back. If something goes wrong in this process--kind of like a mutation in biology--it may not show up right away, but can cause problems down the line.

Is this what your problem is? Dunno, but maybe. Here are the methods I use to deal with this kind of situation.

1) Anecdotally, I've found that pivot tables can be to blame, so if you have a page with a pivot, try deleting that sheet and recreating it on another.
2) If that doesn't work, make a clone. By this, I mean to create a new workbook and copy/paste the contents of each worksheet into new sheets on the new workbook. Do NOT right click the worksheet tab and just copy the whole tab over.
3) From now on, save in .xlsb format instead of .xlsm. You can still use macros with xlsb, but since it's in binary form, not xml form, the filesize is much smaller, and less prone to corruption.

Good luck!

 

[NWSanta]

Ok so I've done some more digging into the file and perhaps it is what they are doing that is the problem. The problem is definitely tied to the comments. I didn't realize they were doing this but, here is what they are doing.
They are using the "fill effect" in the comment tab and then choosing a picture and then linking to a file that resides on a network share.
Clear as mud?
Now this all works fine and dandy however, when the cel that has the comment in it is deleted, it's fine.
When the comment itself is deleted the file is garbage.
As soon as you delete one comment it causes it to crash and when it rebuilds it deletes all of the comments that have this feature/function in them.

So the plot thickens!

 

[Smitty]

Welcome to the Board!

Have you considered Access or PowerPivot? A 60 Mb workbook is going to be unstable at best no matter what you do.

 

[NWSanta]

Thank you for that, now can you pound that into my employees???? I've been telling them for the last 6 months that they are using this like a database, instead of a spreadsheet!!!

 

[Fazza]

Thank you for that, now can you pound that into my employees???? I've been telling them for the last 6 months that they are using this like a database, instead of a spreadsheet!!!

Good luck