Home » Forums » Open Source Learning Center » Members Show and Learn Forum

My loaded excel takes a lot of time to load

Submitted by laks on 28 December, 2008 - 17:04

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

‹ Another solution to compare 2 lists
»
Nick's picture

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

Submitted by Nick on 29 December, 2008 - 16:41.

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

Submitted by laks on 29 December, 2008 - 17:38.

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

Submitted by Nick on 29 December, 2008 - 17:42.

Have you tried with calculation on manual ?

»

Caluclation: Manual seems to help

Submitted by laks on 30 December, 2008 - 02:02.

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

Submitted by laks on 30 December, 2008 - 03:00.
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
»