PDA

View Full Version : Export / Print all Charts in Workbook to PDF



nb-
04-10-2014, 10:20 AM
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
04-10-2014, 01:39 PM
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-
04-11-2014, 02:42 AM
Thanks.

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

p45cal
04-11-2014, 02:51 AM
Any idea why its seems necessary to have the object as the active object?
None!