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
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