Export / Print all Charts in Workbook to PDF

[nb-]

I am trying to export a load of charts from a workbook to PDF files. For some reason, which I cant seem to spot, when I sue the below code, it exports the whole sheet to a PDF file, not the the chart. Any ideas?

Also if anyone has any ideas how to make the charts auto scale to fit the paper size nicely (without moving them to a new chart sheet) that would be really useful.

Sub export_to_pdf()
'// Outputs all charts to PDF files in specified folder, folder must exist, files must not; no checks (yet)!!!


Dim fp As String
Dim ct As ChartObject
Dim ws As Worksheet

Dim str_book_name As String
Dim str_chart_name As String

Dim str_export_path As String
Dim str_out_name_path As String

Dim i As Long
i = 1

str_export_path = "C:\Charts\"

str_book_name = ActiveWorkbook.Name
str_book_name = Split_text(str_book_name, 1, " ")

For Each ws In ActiveWorkbook.Sheets
    For Each ct In ws.ChartObjects
    
        str_out_name_path = str_export_path & str_book_name & "_" & i & ".pdf"
        
        '// Chart size set to A3 print area aspect ratio (72 points per inch) - still looks a bit off....
        ct.Width = 1089
        ct.Height = 734
        
        '//Setup the charts print area
        With ct.Chart.PageSetup
            .ChartSize = xlFullPage
            .Orientation = xlLandscape
            .PaperSize = xlPaperA3
        End With
                
        ct.Chart.ExportAsFixedFormat Type:=xlTypePDF, Filename:=str_out_name_path, Quality:=xlQualityStandard, IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
        
        Debug.Print str_out_name_path
        
        i = i + 1
    Next ct
Next ws

End Sub



Public Function Split_text(str, n, sepChar)

Dim x As Variant
    
x = Split(str, sepChar)

If n > 0 And n - 1 <= UBound(x) Then
    Split_text = x(n - 1)
Else
    Split_text = ""
End If

End Function

[p45cal]

try adding:

 ct.Activate

just before the line:

ct.Chart.ExportAsFixedFormat Type:=xlTypePDF, Filename:=str_out_name_path, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

[nb-]

Thanks.

Any idea why its seems necessary to have the object as the active object?

[p45cal]

Any idea why its seems necessary to have the object as the active object?

None!