Get name of chart for current pivot table

Question

I have an Excel spreadsheet with four pivot tables and each has a related chart. Using VB.Net I update the Data Source which updates the pivot tables which automatically updates their related charts. All this works great. But now I have to update the title in each chart.

What I think I need to do is access the charts by knowing the name of their related pivot table.

I tried looping through worksheet.pivotTables to get each PivotTable name but get an error at the For Each line:

For Each xlPivotTable As Excel.PivotTable In xlWorkSheetShippedSQ.PivotTables
            strCurrentPivotTable = xlPivotTable.Name

Next

At xlWorkSheetShippedSQ.PivotTables it displays

implicit conversion from object to ienumerable

and wants me to use CType(xlWorkSheetShippedSQ.PivotTables, IEnumerable(Of Excel.PivotTable)) but when I do this I get an InvalidCastException. xlWorkSheetShippedSQ is the current worksheet and I've successfully used it in other places.

Is there a way to access each chart to change each name? if so please share how?

Re-read my answer to your last question. PivotTables is a function that returns one of two types. Select the proper type. — TnTinMn
@TnTinMin. I re-read your answer and found the usage and difference between CType & TryCast in the link you provided. After using TryCast it worked as it did not return an error. Thanks again. — Tilting Code

Tilting Code Tilting Code · Accepted Answer · 2018-09-07T17:32:15

@TnTinMn pretty much supplied the answer in another question they answered for me. As I mentioned in my last comment using TryCast worked as it did not return an error.

Get name of chart for current pivot table

1 Answers