Large Excel file taking hours to open

[sodaniel]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello all,

My company has created an Excel file that, as an .xlsm file, takes 2 hours to open. After that, the functions appear to work as written. The spreadsheet was written on and opens fine in Excel 2007 for Windows. The file is 12 MB in size. I'm opening it now and will try saving as .xlsx and see if it still has a long open time. We're saving as .xlsm for some navigation macros on one sheet.

Has anyone seen anything similar? If so, how have you worked around it or worked with it?

Thanks,

-Sam O'Daniel

 

[JE McGimpsey]

My company has created an Excel file that, as an .xlsm file, takes 2 hours to
open. After that, the functions appear to work as written. The spreadsheet
was written on and opens fine in Excel 2007 for Windows. The file is 12 MB in
size. I'm opening it now and will try saving as .xlsx and see if it still has
a long open time. We're saving as .xlsm for some navigation macros on one
sheet.

Has anyone seen anything similar? If so, how have you worked around it or
worked with it?

2 hours is definitely about 2 orders of magnitude too long.

Are there images in the file? Pivot tables? Embedded objects/files?

Did conversion help?

 

[sodaniel]

Hi J.E. - I agree, it's way too long. Unfortunately, conversion did not help. I saved as .xlsx and it still took 2 hours to open.

I can't speak exactly to the nature of pivot tables/embedded objects as I'm just the IT person and it was written by someone else. It does have some images, but they're just our company's logo. I'll check on the pivot/embedded and get back to you.

Thanks,

-Sam

 

[Pat McMillan]

Is there any chance you could share the file with us to investigate? If so,
please let me know and we can figure out how to get the file to me.

Thanks,

Pat McMillan
Microsoft Corp.
(e-mail address removed)

 

[sodaniel]

@J.E. - I'm informed that the spreadsheet does not have any memory intensive (time consuming) items: tables (array formulas), objects, pivot tables, or embedded objects that are memory hungry. I'd expect it to slow down on Windows as well, but who knows.

@Pat - having lurked on these boards before, I anticipated you showing up and asking that. I have already asked our developer toward that and there is a concern about the data contained in the sheet. I'll check and see what we can do. Do you have any policies on NDAs or the like?

Thanks so much, everyone.

-S

 

[Pat McMillan]

Thanks Sam. I understand the issue of data privacy. If it's realistic,
perhaps you could change all text in the document to bogus values so that
the data becomes meaningless (although I know that can take a lot of time).

Other questions I would ask:
Is there any chance the file containst VLOOKUP functions?
In the process of creating the workbook is there any chance that some data
was pasted from web pages? We're aware of an issue when certain html when
pasted into Excel can leave remnant (but invisible) objects in Excel. If the
process is repeated many times you can end up with thousands of invisible
objects in the workbook, which can slow things down significantly.

Thanks,

Pat

 

[sodaniel]

Hi Pat,

Our Excel developer told me that there are no VLOOKUPs or pasted HTML code.

He's going to work on creating a version of the file that we can send to you. I'll pass it along once I've tested it. Are there any other details you'll need to know?

Thanks again,

-Sam

 

[Pat McMillan]

Thanks Sam. Since there are so many things that can impact performance, a
file with the problem will definitely be the best way for us to investigate.
Feel free to contact me directly to arrange getting the file to me.

Thanks,

Pat
(e-mail address removed)