Diagnosing slow Excel opening on one particular spreadsheet

[unigee]

I have spent several weeks trying to understand why a particular spreadsheet we have takes around 5 minutes to open.

Doubleclicking the excel file shows the normal Excel 2013 splash scren.
It immeditely shows it has opened at 100%
The familiar Excel splash screen will now whirl away from aproximetely 5 minutes before it actually is open

Once open, it will be nice and quick and completely useable.

Background of the spreadsheet:

1. It has 190 worksheets inside
2. It's full of shapes, charts and pictures
3. It has lots of hyperlinks (to help navigate the spreadsheet)
4. It does not use calculations, all information is either static or content from other excel files

The most obvious to me for the cause of the slowness is item 4 and 1, however after the testing outlined below this does not seem to be the cause.

What I have tried to do to troubleshoot it (using VBA)

1. Broken every link
2. Deleted every Shape, chart and picture from all worksheets
3. Deleted all the hyperlinks
4. Deleted all the text in each of the worksheets

What I essential have now is a blank workbook with 190 worksheets

This workbook still takes 5 minutes to open.

What I did notice is that if I deleted all the sheets but 1, then made a copy of that single remaining sheet, there was a very very slight refresh lag.
When I created a brand new sheet, and made a copy, I did not notice any lag whatsoever.

I believe there is something corrupted within the worksheet, and as it has been copied over hundreds of times it has made the issue worse over time, but finding it very difficult identifying what it is.

Fustratingly, I have another workbook that has similar content and number of worksheets but this one opens instantanously.
Comparing the two has not proven useful yet.

Anyone else know what could cause slow loading times?

Is there any tools to help diagnose this?
I have opened the file in 7Zip and noticed there are 500 'PrinterSettings'.bin files. perhaps this could be a reason, but don't know how to delete these.
I have opened the file in Open XML SDK productivity tool, again looking at this has not helped and the sheer number of XML nodes makes it almost impossible to troubleshoot.


Thanks

 

[Johnny C]

In 7Zip just select the files and hit the Delete key

In 7Zip, look for the external links (\xl\externalLinks\) and try deleting all the files in there. Even though you've broken the links, XL might try and refresh a cache of the links it has in there even if they aren't referred to any more.

Failing that, as you're clearly adept at VBA, write some code to rebuild the sheet in a new workbook. Rather than paste formulas into the new book loop through cells and rows and copy the formula text or you might end up with a ton of links to the original

 

[unigee]

Thanks Johnny c

I tried deleting the files in 7Zip, but this cause the file to corrupt and Excel could not recover.
Didn't think of the \xl\externalLinks will give that a go


I have considered completely re-creating the workbook from VBA
The workbook does not use calculations or formulas (except really basic =A2 type formulas), its completely full of shapes and text.
I have tried simply copying all worksheets using VBA, but this did not solve the problem.

Like you said, I think I would need to loop through everything on each worksheet and essentially re-make it from scratch.