VBA/Excel ChartTitle Problem

[threadgill]

Hi, this may be very confusing at first, but I'm going to do my best.

I have a slightly complicated Excel spreadsheet that has lots of VBA macros used in it. I'm trying to add a macro that finds all charts in the workbook and then exports them as a "PNG" file. My code works perfectly for 8 worksheets, which contain the majority of the charts anyway.

I have 20 worksheets in the workbook that contain a varying amount of charts (1 to 5) on their page. The charts are not in sequence, their top left corner is always in the same column; however, the row is always different.

Here's the problem...on these 20 worksheets, it only exports the first chart. I've debugged the code to see when the chart title is being updated on these sheets. When I actually look at the charts on the sheet, they all have different titles and are in very different locations. According to "ActiveSheet.ChartObjects", the number of charts is correct (in every one of the 20 worksheets); however, the 'Item#' (each individual chart) points to only the first chart on the page. None of the other charts seem to be found by the "ActiveSheet.ChartObjects" call. So, if the "ActiveSheet.CharObjects" shows that there are 5 charts on one worksheet, then the first chart will be exported 5 times (only b/c I use the increment number in the name).

If you'd like to see my spreadsheet (it's monstrous), I'll share it; but I don't know that it would help much. It seems that the problem is mainly with Excel and not with VBA. As I've stated, my code works perfect on all of my other worksheets in the workbook (one of which contains 61 different charts).

Thanks for any tips that you can give. I've never run across this before in any other macros I've written, but, then again, I'm fairly new to creating macros in Excel.

Here's the code I'm using:

Sub ChartExport()
Dim myPath As String
Dim fName As String
Dim x%
Dim i As Integer
Dim ch As Chart
Dim Pict As Object
 
    Application.DisplayAlerts = False
    myPath = ActiveWorkbook.Path
    For x% = 1 To Sheets.Count
        If ((Sheets(x%).Name <> "Home") And (Sheets(x%).Name <> "Summary")) Then
            Sheets(x%).Select
            i = 1
            For Each Pict In ActiveSheet.ChartObjects
               Set ch = Pict.Chart
               fName = myPath & "\" & ch.ChartTitle.Caption & "_" & i & ".png"
               ch.Export fName, FilterName:="PNG"
               Set ch = Nothing
               i = i + 1
            Next
            Set ch = Nothing
        End If
    Next x%
End Sub