• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Slow Opening Excel File

My excel file used to open up faster, and now it's significantly slower.


It has about the same amount of macro code as another file (and the other one is still tip top).


Could anyone provide any tips on what causes this?
 
Check that any network links or files linked to are in the same location and haven't been moved or deleted.


Do you have sharing enabled for that workbook, that can cause bloat and slowness.
 
I tried your suggestion (Hui), and I've read various forums to no avail. I cleared the contents and deleted everything on the slow opening excel file, and after I saved-closed-reopened it -it was just as slow?!?


I played with settings doing everything I can think of.


When I make a new file and reimport all the macros it opens quickly fresh tip top, but rest assured after 5 or 6 times of opening the file I can notice it getting slower and slower again.


Why does my excel file get slower and slower like this?
 
Are you using lots of Pivot Tables or Data Tables (the Function) or lots of Volatile Functions like Sumproduct?


Do you want to email it to me?
 
Do you realise you have 105 Connection queries and 119 Named Ranges setup ?

Most of the named ranges refer to ranges with no data


If I remove all but 1 of the Connections the load/save time goes from 4 seconds to less than 1 second


If your macros are generating new connections or named ranges you may want to add some code to remove them after they are used or before you close the file
 
Interesting.


Well the cats definitely out of the bag.


I'm sure you can see my Excel skills are "superior". lol


To be quite honest apparently as you can clearly see I have no idea what I'm doing.


But I would like to know exactly what you mean in more detail.


Is there anyway you could email the file back to me so that can I see what you've removed.


What are connection queries exactly?
 
Open the file


Connections - Are links or definitions to other files using SQL, ODBC etc

Goto Data, Connections

You will see a list of queires named Import-Me86 etc

It looks like they are queries based on Text files on your computer

I just selected them all except 1 and pushed the Remove Button


Named Ranges

Goto Formula, Name Manager

Select all the ones you don't want and use the Delete Button
 
Back
Top