PDA

View Full Version : Print Charts to pdf



pwreconomist
05-22-2018, 09:19 AM
Hi. Very puzzled here, as messages in VBA don't seem to indicate issue. I am trying to get my code to:

1) print each chart in my workbook to a specific directory (code is there)
2) over-write any file name that already exists (code not there yet)
3) not open the file once saved (code is there)
4) print each chart with the chart name as the filename (code not there yet)

So I am doing this step by step. I managed to get the charts to print to a printer, but then commented out the print line and replaced with a piece of code from microsoft to print to pdf. But this is where it breaks down. Any help would be MUCH appreciated.

Sub PrintEmbeddedCharts()
Dim ChartList As Integer
Dim X As Integer
' Variable chartlist stores a count of all embedded charts.
Dim str_export_path As String
Dim str_out_name_path As String


ChartList = ActiveSheet.ChartObjects.Count
' Increments the counter variable 'X' in a loop.
For X = 1 To ChartList
' Selects the chart object.
ActiveSheet.ChartObjects(X).Select
' Makes chart active.
ActiveSheet.ChartObjects(X).Activate
' Prints one copy of active chart.
' ActiveChart.PrintOut Copies:=1
ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\SavedPDF" & i & ".pdf", OpenAfterPublish:=False
Next
End Sub

pwreconomist
05-22-2018, 09:28 AM
Here is a more "robust" version that tries to include naming each file with the chart name and should overwrite the earlier file saved with the same name (using displayalerts function). Still doesn't want to run correctly.

Sub PrintEmbeddedCharts()
Dim ChartList As Integer
Dim X As Integer
' Variable chartlist stores a count of all embedded charts.
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


Application.DisplayAlerts = False
ChartList = ActiveSheet.ChartObjects.Count
' Increments the counter variable 'X' in a loop.
For X = 1 To ChartList
' Selects the chart object.
ActiveSheet.ChartObjects(X).Select
' Makes chart active.
ActiveSheet.ChartObjects(X).Activate
' Prints one copy of active chart.
' ActiveChart.PrintOut Copies:=1
str_export_path = "\\c01u03\c01u03\cgk1870\My Documents\_2018\ADSS General Work"
str_book_name = ActiveWorkbook.ActiveChart.ChartTitle
str_out_name_path = str_export_path & str_book_name & "_" & ".pdf"
ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=str_out_name_path, OpenAfterPublish:=False
Next

Application.DisplayAlerts = True
End Sub

Kenneth Hobs
05-23-2018, 10:07 AM
str_export_path = "\\c01u03\c01u03\cgk1870\My Documents\_2018\ADSS General Work\"