2007 vs 2010 - Large number of tabs

[peirr]

Guys,

We've been upgrading a number of uses from Office 2007 to 2010, on the whole this has gone okay, however, one user is having problems with one specific spreadsheet. When she attempts to open this spreadsheet on her PC (With Office 2010) she receives the error message:

"Excel cannot complete this task with available resources. Choose less data or close other applications"

The file opened fine in 2007 Excel.

I attempted to open the file (firstly from the network share it resides on and then locally from a copy on my desktop), I received the same error, the machine I was trying to open it on is pretty beefy- 8GB RAM, 8 processors etc.

I tried to open the file in Excel 2007 on one of our laptops (of very moderate/low spec) and it opened just fine in 2 ticks.

The file in question is rather odd in that it has a very large number of tabs (think ~ 300, I stopped counting at 140), each one doesn't have a huge amount of data though - the file is only about 4 mb.

Any thoughts on this would be much appreciated - I'm guessing it's just a change to the rendering engine which MS have made which in this instance cause problems, however, any other thoughts are most welcome Thanks!

 

[ChrisR]

Hi,

Do you get the same message whether the file is saved in .xls or .xlsm format? 4mb is not a large file so it definitely seems some sort of conversion issue.

I know sometimes with the new excel people are in the habit of highlighting entire rows/columns etc.............Due to the massive number of cells in 2010, I've saw this increase the file size 10 fold and it decreases once the formatting was removed, but at 4mb you don't seem to have this issue.

Regards,
Chris

Regards,
Chris

 

[peirr]

Hi,

Do you get the same message whether the file is saved in .xls or .xlsm format? 4mb is not a large file so it definitely seems some sort of conversion issue.

I know sometimes with the new excel people are in the habit of highlighting entire rows/columns etc.............Due to the massive number of cells in 2010, I've saw this increase the file size 10 fold and it decreases once the formatting was removed, but at 4mb you don't seem to have this issue.

Regards,
Chris

Regards,
Chris

The same problem persists as an xlsm, I can't save as an xls due to all the conditional formatting she's added into the sheet, interestingly enough a colleague has a sheet with similar data but without any calculations and this seems to open and work just fine... my next attempt will be to open on the laptop again and turn off automatic formula evaluation and see if that has any impact.

Thanks for the reply Chris.

 

[ChrisR]

No problem Peirr,

I wish I could be more help, but unfortunately these type of things are usually just trial and error!

Good luck!

 

[1968rm]

I have the same issue. My co-worker has been upgraded to excel 2010, and she is having the same issue with a file - rather large (over 50MB), not that many sheets (about 40) but there is a ton of information and calculations in there... In 2007 the file opens easily.
My co-worker says she just clicks ok to all error messages. I am curious if there is anything I can do to that workbook to make it open without errors.

 

[ChrisR]

What I did with mine was highlighted all columns and rows that I knew should be blank (I.e. No data or formatting should be present) and took off all formatting and then deleted these cells.

I had a spreadsheet that went from 4mb to 28 mb and this was the problem..............I'm not saying it wil be yours but at least it gives you an option

 

[1968rm]

I know that the formatting there is on entire columns, not only on the used range. But does 2010 have more cells per sheet than 2007?

 

[ChrisR]

I think 2007 and 2010 have the same number of rows and columns.

The issue usually occurs if the spreadsheet was saved in an old format E.g. .xls and then when transferred across was saved as a .Xlsx or .Xlsm

As I believe the .Xls only supported 65k rows or something? So by converting across to .Xlsm or .Xlsx I assume it then adds an additional 1 million rows x 16000 columns or something per worksheet............So if entire row or column formatting was used then it could definitely create an issue I would have thought..........

Just my two cents of course!

 

[1968rm]

This workbook has been converted to xlsm a while ago, and works well in 2007. Issue is only with 2010. (I might still try your formatting anyways, just to rule it out.)

 

[ChrisR]

Okay, if it has been .xlsm for a while you should be fine. Definitely worth keeping an eye out for though as I've had this happen on two files and that was the reason.