Workbook takes 30 minuts to start, how can I make it quicker?

[jtpryan]

I have a workbook that takes a half hour to start. I have looked high and low and can't seem to get it to load any faster. Anybody have knowledge of any utilities I can use to track what is taking so long? It has 13 sheets in it and 7 external connections, primarily to SharePoint sites (.mht files). I know this isn't a lot of info, but if you ask I will try to provide what you need to help. I really don't know what to look for at this point. This is a workbook written by somebody no longer here.

Thanks
Jim

Anybody know of a utility that might produce a log file of what Excel is doing on startup? That part where it says it is up to xxx% open? I'm trying to figure out what exactly takes so long. I'm using Process Monitor but that just tells me things at a system level.

Jim

 

[RCBricker]

13 sheets is not very many sheets, but if each sheet has 1,000,000 rows and 100 columns then it is a ton of data to be loading from anywhere (esp. loading anywhere not your C:\).

7 external links could be the problem unless you mean they are hyperlinks. If they are pulling information (i.e. - cell A2 = link to cell in another workbook), then this will cause some lag.

If the workbook has a ton of formulas, esp. if they are big complicated formulas, then this can cause lag.

So your possible issues for loading are:

workbook or links to other workbooks not on your C:\
External links
Formulas
unreasonably complicated formulas
Formulas or unreasonably complicated formulas that reference data in other workbooks
Tons of data. More data more load time.

All that said 1/2 an hour to load is quite a bit.

HTH

 

[liveinhope]

I cant help with an actual utility but I just have a few suggestions

suggestion 1 - edit the links to the files. so that your workbooks doesn't automatically update to reflect changes in those files when you open the workbook in 2007 that's office button -> prepare -> edit links to files -> startup prompt -> don't display the alert and don't update automatic links. save and close your workbook. then open it again . how long did it take to open this time ? now ,update the links one at a time by highlighting one link and clicking "update values" . record how long each link takes to update . are all the links slow or is there a "rogue" link

suggestion 2- your post suggests this is a work-related problem so now that you have this data contact your IT team. There may be nothing wrong with your workbook as such but there could be a network issue which no-one on this board can help with. the timings you records in suggestion 1 will be useful

suggestion 3 - (leave this for the moment until you have followed up suggestions 1 and 2) maybe there are one or more macros that run before your file is ready to use.

 

[jtpryan]

Thank you all and sorry for the late response.

This is Office 2016 and I can't find and "prepare" area. Also, I don't believe it is an system/network issue as we have a ton of much larger and more complicated workbooks with more links that open in 5 min or less. But good suggestion nonetheless.

Here is what I have found that is interesting. I removed one sheet at a time from the workbook, closed it and opened it, noted the time, put that sheet back, and did the same with the next sheet. I found one sheet that is the culprit. With this sheet out the workbook opens in 5 min. Put it back, 30 min. Then I took that sheet and did a copy/move to a new workbook all by itself. That new workbook takes about 23 min to open. The odd thing is that sheet really has very little in it. I am just doing this now and deleting sections of the sheet to see if I can narrow it down that way.

More to follow...

Jim

 

[PaddyD]

utility suggestion:

Why FastExcel V3 - Decision Models

 

[liveinhope]

you've found one worksheet that makes opening the workbook slow ... and that was what I expected you might find .

I don't have office 2016 so I don't know exactly where you go in excel 2016 to get list of links that file has to other files - do you know how to do that is excel2106