Exporting all Charts from a worksheet VBA working

[aabrahamson]

Hi All,

I have a great VBA that exports all charts from a workbook and I wanted to change it to do all charts from a worksheet. I thought I could just take out the first loop (For & Next) no such luck see below working code that I wish to modify.

Sub ExportAllChartsjpg()
    Dim ch As Chart
    Dim objChartObject As ChartObject
    Dim ws As Worksheet
    Dim strExportPath As String
    Dim strFileName As String
    Dim lngWSChartsCount As Long
    
    strExportPath = ThisWorkbook.Path    'Change if you want different path


    'Export charts from worksheets
    lngWSChartsCount = 0
    For Each ws In Worksheets
    ws.Activate
    
        For Each objChartObject In ws.ChartObjects
            Set ch = objChartObject.Chart
            On Error Resume Next
            strFileName = ch.ChartTitle.Text & ".pdf"
            ch.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strExportPath & "\" & strFileName
            If Err <> 0 Then
                Err.Clear
                strFileName = ch.ChartTitle.Text & ".pdf"
                ch.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strExportPath & "\" & strFileName
                If Err = 0 Then
                    lngWSChartsCount = lngWSChartsCount + 1
                End If
            Else
                lngWSChartsCount = lngWSChartsCount + 1
            End If
            On Error GoTo 0
            strFileName = vbNullString
        Next objChartObject
    Next ws
  
    Call MsgBox(lngWSChartsCount & " charts from worksheets exported to " & strExportPath, vbInformation, "Charts Export Result")
    
End Sub

 

[RickXL]

Hi,

I think this might do it:

Sub ExportAllChartsjpg()
    Dim ch                As Chart
    Dim objChartObject    As ChartObject
    Dim ws                As Worksheet
    Dim strExportPath     As String
    Dim strFileName       As String
    Dim lngWSChartsCount  As Long
    
    strExportPath = ThisWorkbook.Path    'Change if you want different path


    'Export charts from worksheet
    lngWSChartsCount = 0
    Set ws = Worksheets("Sheet1")
    
    For Each objChartObject In ws.ChartObjects
        Set ch = objChartObject.Chart
        On Error Resume Next
        strFileName = ch.ChartTitle.Text & ".pdf"
        ch.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strExportPath & "\" & strFileName
        If Err <> 0 Then
            Err.Clear
            strFileName = ch.ChartTitle.Text & ".pdf"
            ch.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strExportPath & "\" & strFileName
            If Err = 0 Then
                lngWSChartsCount = lngWSChartsCount + 1
            End If
        Else
            lngWSChartsCount = lngWSChartsCount + 1
        End If
        On Error GoTo 0
        strFileName = vbNullString
    Next objChartObject

  
    Call MsgBox(lngWSChartsCount & " charts from " & ws.Name & " exported to " & strExportPath, vbInformation, "Charts Export Result")
    
End Sub

In the

Set ws = Worksheets("Sheet1")

line you need to change Sheet1 to be the name of the sheet to export the charts from.

Basically, the For Each construct does a Set for each member of the collection. Using just Set allows you to just restrict the process to one sheet.

 

[aabrahamson]

Thanks I found one other issue that kept them from making single pdf for each chart here is the final VBA if anyone else wants to use it

Sub ExportChartsPDF()
    Dim ch As Chart
    Dim objChartObject As ChartObject
    Dim Ws As Worksheet
    Dim strExportPath As String
    Dim strFileName As String
    Dim lngWSChartsCount As Long
    




    strExportPath = ThisWorkbook.Path    'Change if you want different path




    'Export charts from worksheets
    lngWSChartsCount = 0
    Set Ws = Worksheets("Results")
    
    


        For Each objChartObject In Ws.ChartObjects
            objChartObject.Activate
            Set ch = objChartObject.Chart
            Ws.Activate
         
            On Error Resume Next
            strFileName = ch.ChartTitle.Text & ".pdf"
            ch.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strExportPath & "\" & strFileName
            If Err <> 0 Then
                Err.Clear
                strFileName = ch.ChartTitle.Text & ".pdf"
                ch.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strExportPath & "\" & strFileName
                If Err = 0 Then
                    lngWSChartsCount = lngWSChartsCount + 1
                End If
            Else
                lngWSChartsCount = lngWSChartsCount + 1
            End If
            On Error GoTo 0
            strFileName = vbNullString
        Next objChartObject
    


    
    Call MsgBox(lngWSChartsCount & " charts from worksheets exported to " & strExportPath, vbInformation, "Charts Export Result")
    




End Sub

here was the issue

            objChartObject.Activate
            Set ch = objChartObject.Chart
            Ws.Activate

 

[RickXL]

I tried it out with a couple of sheets and charts and my version worked OK. What was going wrong?

I ask because you hardly ever need to use Select or Activate in VBA if your code is slick enough.

Regards,

 

[aabrahamson]

IT was making all the charts in all the pdfs so every pdf had the 6 charts in them. seems I need select the next chart each time so as to only get one chart per pdf.