bttfa
Posts: 3
Joined: 2010-02-10
|
My workbook has a worksheet with 200,000 rows. My code creates a PivotTable. There are many rows (unique dates) in the PivotTable. I want to hide most of them (I only want to see a recent week of data). Manually unchecking those dates in the PivotTable is fast. Using VBA is even faster. But looping in VB inside my AddinModule.vb is extremely slow. Here is an excerpt from my code;
Dim pvt As Excel.PivotTable = wsj.PivotTableWizard( _
SourceType:=Excel.XlSourceType.xlSourceSheet, _
SourceDat a:=wsj.UsedRange, _
TableDestination:=wsPivot.Range("a4"), _
TableName:="PivotDollars") 'Creates PivotTable
Dim pvtRow As Excel.PivotField = CType(pvt.PivotFields("InvoiceDate"), Excel.PivotField) 'Row
With pvtRow
.Orientation = Excel.XlPivotFieldOrientation.xlRowField
.Position = 1
End With
...(column and data created - removed for brevity)...
Dim DisplayStartDate As DateTime = DateValue("5/1/2009")
Dim DisplayEndDate As DateTime = DateValue("5/7/2009")
Dim pvtRowItems As Excel.PivotItems = CType(pvtRow.PivotItems, Excel.PivotItems)
With ExcelApp
.Calculation = Excel.XlCalculation.xlCalculationManual
.ScreenUpdating = False
End With
For Each pi As Excel.PivotItem In pvtRowItems 'this is taking 3 seconds per loop!
If DisplayStartDate > DateValue(pi.Value) Or DisplayEndDate < DateValue(pi.Value) Then
Diagnostics.Debug.Print("pi.value = " & pi.Value)
pi.Visible = False
End If
Next
...(ExcelApp reset, etc.)...
What is the fastest way to edit the Visible Property? Was this covered in the sample code in your FAQ (sorry if I missed it)?
Thanks in advance. |
|
Andrei Smolin
Add-in Express team
Posts: 18793
Joined: 2006-05-11
|
Hi bttfa,
BTW, what's your name?
I'm sorry, this isn't a frequently asked question.
For Each pi As Excel.PivotItem In pvtRowItems 'this is taking 3 seconds per loop!
If DisplayStartDate > DateValue(pi.Value) Or DisplayEndDate < DateValue(pi.Value) Then
Diagnostics.Debug.Print("pi.value = " & pi.Value)
pi.Visible = False
End If
Next
Are you saying that a similar code in VBA works significantly faster? If yes, can you please me some code for testing?
Andrei Smolin
Add-in Express Team Leader |
|
Andrei Smolin
Add-in Express team
Posts: 18793
Joined: 2006-05-11
|
Oh, you can send me the code to the support e-mail address (see readme.txt).
Andrei Smolin
Add-in Express Team Leader |
|