My loaded excel takes a lot of time to load

My xlsx has about 20,30 pivot tables and pivot charts with filtering and custom fields in them. The excel now takes about 2 minutes to open. Can I make it open without refreshing the pivots? (without pressing the esc button?)

Thx
Laks

PS:
Nick, I was looking for a place to ask YOU these questions, like old times. Thanks

Nick's picture

Answer: Sheet with Pivot Tables taking a long time to load

Hey Laks.. thanks for animating the forum !

Try:
right click on pivot table=> pivotTable Options=>Data=>"Refresh data when opening sheet"

Rgds

Nick

Refresh data when opening sheet

That option was unchecked (in excel 2007). I do have the 'Save source data with file' option checked. If any, that shd help not hurt, right?

Nick's picture

Refresh data when opening sheet

Have you tried with calculation on manual ?

Caluclation: Manual seems to help

Yes, manual seems to help. I do need to clean this sheet up. Too many pivot tables and charts and formulas. After all this, I don't want this to break. Thanks for the suggestions

XML maps

In this excel 2007 sheet I import XMLs that my access 2007 writes. (There were some restrictions in excel pivots directly hitting access by SQL, so went this route).

Everytime I run my macro, it will delete some tables and recreated them by importing XMLs. I just checked the XML maps. It had more than 5,000!. I wrote a macro to delete these cml maps and now my excel seems to move much faster.

Should look more into this.

Sub Delete_XMLMaps()
        '
        ' XML maps get created everytime we import XMLs. 
        ' Delete them before importing the next batch.
        '
        Dim ct As Integer, XMLMap
        Const maxXMLDel = 500
        For Each XMLMap In ActiveWorkbook.XmlMaps
            ct = ct + 1
            If ct > maxXMLDel Then Exit For
            XMLMap.Delete
        Next
End Sub