Preparing worksheet (press ESC to cancel)

[JorgeChibante]

Hello,

First of all let me just say this is my first post here, I'm sorry if anything goes against the rules.

I am having a problem with an excel workbook that I can't solve because I'm not really experienced with excel, I'm still learning.

I have a workbook that is quite complex and quite large (around 27mb). This workbook fetches information from a SQL Database and creates several sheets based on the information it fetches. However is not a ridiculous amount of sheets, the exemple I'm talking about has 15 sheets, most of them are hidden, and 7 are visible.
When the workbook is opened it connects to the database fetches the necessary information and does either two things, either creates worksheets or updates some tables on the worksheets if they already exist.

This process takes around (on this particular book that has quite a lot of information, usually they are around 3mb) 3 or 4 minutes. I know that is a lot of time but it's not these 4 minutes that bother me.

The code that runs this update process is on the "Workbook_Open" event and usually takes around 10 to 15 seconds. On this particular workbook it takes more time probably because of the amount of information.
However after this event is over on the status bar I get a message that says "Preparing worksheet (press ESC to cancel)" and it takes maybe around 30 minutes for this to be over. I do not know if excel is doing some calculations or whatever it is doing. I would like to know what it is doing so that I can solve it.

After this I get another message on the status bar showing "Calculating (xx Processors)" for around 30 seconds and after this the file is ready for use.

I would like to know if there is any way of knowing what excel is doing on that "Preparing worksheet" process and how can it become faster.

The file has some conditional formatting and 3 images.

Thanks in advance for your help. Also sorry for my English, is not my main language.

 

[sijpie]

Jorge, welcome, and don't worry about your English. It seems better then some of the native English speaking people...

I would hazard a guess that a lot of time is spent in writing temporary files. if you go tothe folder where the file resides, you can see while all this is happening if Excel writes any temporary files. For starters as a default Excel is set up to write a back-up file. So while it is doing your macro, it might well pause the macro to do a backup and continue. With a 27MB file this is going to be slow. 30 minutes is excessive though. But I get pretty slow response even with 7 MB files sometimes.

So your macro could perhaps at the start stop all the background processes: no back-up files, no automatic calculations, no screenwrites. But some of these may probably only affect the initial 4 minutes DB /WS updating.


Then where is the WB held? on a local drive, network drive? Can you test it on a SSD drive?

Next to check is formulas: Do you use a lot of formulas? Are there any array formulas (the one with the {} brackets) any sumproduct formulas?
These will cause high calculating times. Do you need the formulas or is it possible during the database import to calculate all the required results for the sheets and dumping the results as values. (using memory arrays this can be very fast)

 

[JorgeChibante]

Hello sijpie,

Thank you for your help.

Indeed the columns have a lot of formulas, almost all of them with VLOOKUP. In the meantime I have already sorted a big part of the problem. After the initial process after those 3 or 4 minutes I copy and paste values on most of the formulas, because there is really no reason to have a formula when I just need the values. That has decreased those 30 minutes to around 1 or 2 minutes, which I find acceptable given the size of the file.

However you said I can deactivate the creation of backup files. I know how to deactivate screenupdating and automaticcalculation but I never seen that backup file option. How can I set it?

Again thank you for your help.

 

[sijpie]

with Application.Autorecover
    .Enabled = True    ' Switch on
    .Time = 10           ' set to 10 min interval
    .path = C:\MyPath ' Path where backups are to be stored
end with

https://msdn.microsoft.com/EN-US/library/office/ff837581.aspx