Using Excel VBA, how do you automate the production of a list of chart titles from within the workbook?

Question

I have a Excel workbook full of charts and some data tables on different sheets. I am looking to create a summary of all the chart titles in one sheet.

I have tried some VBA coding but alas I can not quite adapt the code to select and copy across the title of the chart instead of the sheet names.

Set objNewWorkbook = Excel.Application.Workbooks.Add
Set objNewWorksheet = objNewWorkbook.Sheets(1)

For i = 1 To ThisWorkbook.Sheets.Count
    objNewWorksheet.Cells(i, 1) = i
    objNewWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i

I expect a nice list of the titles of the charts. An added bonus would to have the sheet names as well.

I'm away from Excel/IDE right now but pretty sure it's just: ThisWorkbook.Sheets(i).ChartObjects(1).Chart.ChartTitle. If you have more than one chart per worksheet (or if you have 0 charts on a worksheet, or if the chart doesn't have a title, etc.), you'll need to make some modifications to handle those cases. — David Zemens

Darrell H Darrell H · Accepted Answer · 2019-08-30T18:41:13

You can change i to start at whatever row you want and format/add titles. You are working with a collection of worksheets and a collection of chartobjects. You can simply loop through.

Sub ChartList()
    Dim objNewWorkbook As Workbook
    Dim objNewWorksheet As Worksheet
    Dim wks As Worksheet
    Dim cht As ChartObject
    Dim i As Long

    Set objNewWorkbook = Excel.Application.Workbooks.Add
    Set objNewWorksheet = objNewWorkbook.Sheets(1)

    i = 1

    For Each wks In ThisWorkbook.Worksheets
        objNewWorksheet.Cells(i, 1) = wks.Index
        objNewWorksheet.Cells(i, 2) = wks.Name
        If wks.ChartObjects.Count > 0 Then
            For Each cht In wks.ChartObjects
                objNewWorksheet.Cells(i, 3) = cht.Chart.ChartTitle.Text
                i = i + 1
            Next cht
        End If
        i = i + 1
    Next wks

End Sub

Using Excel VBA, how do you automate the production of a list of chart titles from within the workbook?

1 Answers