slow document / pivot table refresh and pivot function

[Justin Larson]

Alright, you excel and VBA geniuses. I call upon your wisdom! This is a tough
question, and I've done a bit of homework already trying to fix it, so I'll
be as detailed as possible.

My problem is simple. When I update a pivot page field, it takes about 15
minutes to update. Ouch!

Here's a description of the document, after that, I'll describe what I've
tried.

I have document called invoice generator. It contains a total of 5 pivot
reports referencing a data table from an outside sheet. The first pivot table
informs the cover sheet, which is used as a monthly invoice for clients. the
others are the same data displayed different ways as "supporting data".

There are two simple VBA sequences. One I recorded with the macro recorder.
All it does is copy the whole workbook and pastes back the values (getting
rid of all links and formulas) and then saves it as a specified name in a
specified folder. I call the sequence exporting the invoice, although
strictly speaking that is not entirely accurate.

The other was code borrowed from contextures at
http://www.contextures.com/excelfiles.html#Pivot
combining PT0016 and PT0015.

What it does is sets two control cells on the cover sheet. I select the
client and the date, and all the pivot tables in the whole workbook update
the page fields to those values, so the invoice is automatically generated
(hence the name).

The source data that informs the pivot tables is huge. Each month, I add
roughly 15-25,000 rows of data, 27 columns wide, all of which is static data.
Not a single formula. Obviously, it did not take long to run out of room on
the datasheet, so each month I move the oldest month of data off the sheet,
and paste the new data to the bottom of the sheet.

My suspicion is that the pivot table cache is somehow piling up and storing
all my old, now-not-even-in-the-source-data data.

So I looked and looked, and found several hot topics. The first was of
course, the issue of volatile functions. This is not the issue. Recalculating
only takes a few seconds. The slowness is only happening when I pivot.

I thought of pivot cache because the size of the file was getting unruly, at
about 89MB. So I looked around and discovered I can share cache for all the
pivot tables. First this was attempted using VBA code from here:
http://www.contextures.com/xlPivot11.html

It didn't make much difference, so I then used the piviottable wizard to
make each of the pivot tables use pivot table 1 as a source. This reduced my
sheet size down to 11MB, but it still performs slowly.

I then tried to unchecked the option to save data with table layout, and
checked refresh on open. I thought that refreshing each time I open the book
may clear whatever is buggering up the workbook. The result was a file size
of only 1904KB, but alas, the pivot page update still takes >10 minutes.

Is there something in any of the code theory that raises red flags (without
having actually seen the code)? I can paste some of it here, but I don't know
how much detail is necessary to provide input. Contextures has been pretty
reliable, so I don't suspect the VBA, although VBA may provide a solution if
I can identify what's slowing me down. I am sure it's something with the
pivot tables, but I can't find any details on how to monitor/troubleshoot
memory/cache/pivot table problems like this yet.

Any comments? More information needed?

 

[Justin Larson]

Alright, just discovered something that may help. It does appear to be the
VBA, because when I go through each sheet and manually update the pivot page
fields, the update is almost instantaneous.

Here's the modified code from contextures. I just retested the control
cells, and teh first time took ~4 seconds (yeah!), the second time it took
just over 1 minutes (aww), the third time it took just over 5 minutes
(fibbity jivet!)

What is happening in this that causes a cumulative slowdown? (Utility is the
Client)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Utility"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B1").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

strField = "Sale_Date"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("C1").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Mike Ward]

Hi, Justin.

I didn't see any responses to your post. Did you find a solution? I have a file that has this same problem. I would be interested in the solution if you have one.

Thank you.

Mike



JustinLarso wrote:

slow document / pivot table refresh and pivot function
02-Apr-09

Alright, you excel and VBA geniuses. I call upon your wisdom! This is a tough
question, and I've done a bit of homework already trying to fix it, so I'll
be as detailed as possible.

My problem is simple. When I update a pivot page field, it takes about 15
minutes to update. Ouch

Here's a description of the document, after that, I'll describe what I've
tried.

I have document called invoice generator. It contains a total of 5 pivot
reports referencing a data table from an outside sheet. The first pivot table
informs the cover sheet, which is used as a monthly invoice for clients. the
others are the same data displayed different ways as "supporting data"

There are two simple VBA sequences. One I recorded with the macro recorder.
All it does is copy the whole workbook and pastes back the values (getting
rid of all links and formulas) and then saves it as a specified name in a
specified folder. I call the sequence exporting the invoice, although
strictly speaking that is not entirely accurate.

The other was code borrowed from contextures at
http://www.contextures.com/excelfiles.html#Pivot
combining PT0016 and PT0015

What it does is sets two control cells on the cover sheet. I select the
client and the date, and all the pivot tables in the whole workbook update
the page fields to those values, so the invoice is automatically generated
(hence the name)

The source data that informs the pivot tables is huge. Each month, I add
roughly 15-25,000 rows of data, 27 columns wide, all of which is static data.
Not a single formula. Obviously, it did not take long to run out of room on
the datasheet, so each month I move the oldest month of data off the sheet,
and paste the new data to the bottom of the sheet.

My suspicion is that the pivot table cache is somehow piling up and storing
all my old, now-not-even-in-the-source-data data.

So I looked and looked, and found several hot topics. The first was of
course, the issue of volatile functions. This is not the issue. Recalculating
only takes a few seconds. The slowness is only happening when I pivot.

I thought of pivot cache because the size of the file was getting unruly, at
about 89MB. So I looked around and discovered I can share cache for all the
pivot tables. First this was attempted using VBA code from here:
http://www.contextures.com/xlPivot11.htm

It didn't make much difference, so I then used the piviottable wizard to
make each of the pivot tables use pivot table 1 as a source. This reduced my
sheet size down to 11MB, but it still performs slowly.

I then tried to unchecked the option to save data with table layout, and
checked refresh on open. I thought that refreshing each time I open the book
may clear whatever is buggering up the workbook. The result was a file size
of only 1904KB, but alas, the pivot page update still takes >10 minutes.

Is there something in any of the code theory that raises red flags (without
having actually seen the code)? I can paste some of it here, but I don't know
how much detail is necessary to provide input. Contextures has been pretty
reliable, so I don't suspect the VBA, although VBA may provide a solution if
I can identify what's slowing me down. I am sure it's something with the
pivot tables, but I can't find any details on how to monitor/troubleshoot
memory/cache/pivot table problems like this yet.

Any comments? More information needed?

Previous Posts In This Thread:

slow document / pivot table refresh and pivot function
Alright, you excel and VBA geniuses. I call upon your wisdom! This is a tough
question, and I've done a bit of homework already trying to fix it, so I'll
be as detailed as possible.

My problem is simple. When I update a pivot page field, it takes about 15
minutes to update. Ouch!

Here's a description of the document, after that, I'll describe what I've
tried.

I have document called invoice generator. It contains a total of 5 pivot
reports referencing a data table from an outside sheet. The first pivot table
informs the cover sheet, which is used as a monthly invoice for clients. the
others are the same data displayed different ways as "supporting data".

There are two simple VBA sequences. One I recorded with the macro recorder.
All it does is copy the whole workbook and pastes back the values (getting
rid of all links and formulas) and then saves it as a specified name in a
specified folder. I call the sequence exporting the invoice, although
strictly speaking that is not entirely accurate.

The other was code borrowed from contextures at
http://www.contextures.com/excelfiles.html#Pivot
combining PT0016 and PT0015.

What it does is sets two control cells on the cover sheet. I select the
client and the date, and all the pivot tables in the whole workbook update
the page fields to those values, so the invoice is automatically generated
(hence the name).

The source data that informs the pivot tables is huge. Each month, I add
roughly 15-25,000 rows of data, 27 columns wide, all of which is static data.
Not a single formula. Obviously, it did not take long to run out of room on
the datasheet, so each month I move the oldest month of data off the sheet,
and paste the new data to the bottom of the sheet.

My suspicion is that the pivot table cache is somehow piling up and storing
all my old, now-not-even-in-the-source-data data.

So I looked and looked, and found several hot topics. The first was of
course, the issue of volatile functions. This is not the issue. Recalculating
only takes a few seconds. The slowness is only happening when I pivot.

I thought of pivot cache because the size of the file was getting unruly, at
about 89MB. So I looked around and discovered I can share cache for all the
pivot tables. First this was attempted using VBA code from here:
http://www.contextures.com/xlPivot11.html

It didn't make much difference, so I then used the piviottable wizard to
make each of the pivot tables use pivot table 1 as a source. This reduced my
sheet size down to 11MB, but it still performs slowly.

I then tried to unchecked the option to save data with table layout, and
checked refresh on open. I thought that refreshing each time I open the book
may clear whatever is buggering up the workbook. The result was a file size
of only 1904KB, but alas, the pivot page update still takes >10 minutes.

Is there something in any of the code theory that raises red flags (without
having actually seen the code)? I can paste some of it here, but I don't know
how much detail is necessary to provide input. Contextures has been pretty
reliable, so I don't suspect the VBA, although VBA may provide a solution if
I can identify what's slowing me down. I am sure it's something with the
pivot tables, but I can't find any details on how to monitor/troubleshoot
memory/cache/pivot table problems like this yet.

Any comments? More information needed?

RE: slow document / pivot table refresh and pivot function
Alright, just discovered something that may help. It does appear to be the
VBA, because when I go through each sheet and manually update the pivot page
fields, the update is almost instantaneous.

Here's the modified code from contextures. I just retested the control
cells, and teh first time took ~4 seconds (yeah!), the second time it took
just over 1 minutes (aww), the third time it took just over 5 minutes
(fibbity jivet!)

What is happening in this that causes a cumulative slowdown? (Utility is the
Client)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Utility"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B1").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

strField = "Sale_Date"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("C1").Address Then

For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub




:

EggHeadCafe - Software Developer Portal of Choice
Add Windows GridView control in WPF dynamically in C#.
http://www.eggheadcafe.com/tutorial...1-bf33de76bd69/add-windows-gridview-cont.aspx