Can't iterate over PivotTables from dotnet add-in

[kfarris9]

I am trying to go through all the pivot tables in a worksheet to display
their names on a form. The MSDN doc tables about a PivotTables interface and
method. But the only method on the Worksheet class is PivotTables (int
index) which returns a single PivotTable. There doesn't seem to be a way to
get the count through the Office2003 PIA for Excel.

 

[XL-Dennis]

Hi,

Yes, it's true that there only exist one event for single pivottables in
terms of Worksheet_Pivottable_Update.

Anyway, the following snippet VB.NET code should get You started in order to
understand how to iterate through a collection on Pivottables.

'On top in a VB module and with a reference to Excel 11 PIA:
Imports Excel = Microsoft.Office.Interop.Excel

Dim xlApp As New Excel.Application
Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Open("c:\TestPT")
Dim xlWSheet As Excel.Worksheet = CType(xlWBook.Worksheets(1), _
Excel.Worksheet)
Dim xlPTable As Excel.PivotTable

'Iterate through the worksheet's collection of Pivottables.
For Each xlPTable In CType(xlWSheet.PivotTables, Excel.PivotTables)
With xlPTable
MsgBox(.Name.ToString & ": " & .DataBodyRange.Address)
End With
Next

With xlApp
.UserControl = True
.Visible = True
End With

xlPTable = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing

You will also find additional info about Pivottables here:
Create Pivottable-reports:
http://www.excelkb.com/article.aspx?id=10172&cNode=7T5V0N

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[kfarris9]

Unfortunately this doesn't work in C#. The PivotTables property on the
Worksheet is defined with an optional parm in VB.net. C# requires the parm.
I've tried null, but that doesn't seem to work. Any suggestions on other
values to try or other alternatives?

 

[kfarris9]

Solved. This answer is to pass Type.Missing as the optional parm on
sheet.PivotTables(Type.Missing)

 

[XL-Dennis]

Thanks for coming back and good You managed to solve it.

The explicit requirement of parameters in C# holds me back to develop with
that tool. Classic VBA/VB is more 'forgiving' and VB.NET permit us to leave
out all the unwanted parameters.

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/