Loop through Chart objects in Excel workbook

Question

I have seen plenty of code that loops through's in an individual worksheet, however I am trying to loop through charts that are individual sheets in the workbook. My code is as follows (as you can see I am simply trying to save the charts as PDFs):

Sub ExportAllCharts()
Dim Current As Worksheet

' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets

   If TypeName(Current) = "Chart" Then
       objCht.ExportAsFixedFormat xlTypePDF, Filename:= _
               "c:/" & Current.Name, Quality:=xlQualityStandard, _
               IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
   End If

Next
End Sub

Unfortunately, when the code loops through all the sheets in the workbook, for some reason it is skipping the charts. Is there an equivalent to Worksheets that applies to individual charts in Excel? Or is there perhaps another way of doing this that I haven't thought of?

Thanks! David

Stand alone charts are not worksheets, they are chart sheets, or simply charts. That is why @tendim's answer works. — Jon Peltier

tendim tendim · Accepted Answer · 2015-02-10T14:50:26

I've just tried a quick test with this code, and it printed the Chart worksheets:

Public Sub test()
    Dim oChart As Chart

    For Each oChart In ActiveWorkbook.Charts
        Debug.Print oChart.Name
    Next
End Sub

That should do what you need. FYI a good place to start is with the VBA object browser, View -> Object Browser or F11 in the Excel Visual Basic Editor.

Hope that helps.

Loop through Chart objects in Excel workbook

1 Answers