Solution using Word as a helper application
As far as I know, there is no way by just using .ExportAsFixedFileFormat but what you desire is possible using Word as a helper application, as I will demonstrate in the following code.
To make exporting a bunch of charts not constantly open and close Word I implemented a ShapeExporter Class, that holds an instance of Word and uses it for exporting the charts or shapes:
Usage in a normal module, if the charge is an embedded chart (chart in a worksheet)
Sub ExportChartToPDF()
' Setting up the variables for passing to ShapeExporter
Dim MyChart As Object
' If your chart is an embedded chart in a worksheet
Set MyChart = ThisWorkbook.Worksheets("YourWorksheet").ChartObjects("ChartName")
' If your chart is it's own "chart sheet" like in os's question:
Set MyChart = ThisWorkbook.Charts("ChartSheetName").ChartArea
Dim fileName As String
fileName = "TestExport"
Dim filePath As String
filePath = ThisWorkbook.Path
' Creating an instance of our ShapeExporter:
' During the creation of the object, Word is opened in the background
' if it wasn't already open.
Dim oShapeExporter As cShapeExporter
Set oShapeExporter = New cShapeExporter
' Export as many shapes as you want here, before destroying oShapeExporter
' The ExportShapeAsPDF method pastes the chart in a word document, resizes the
' Document to be exactly the size of the chart and then saves it as PDF
oShapeExporter.ExportShapeAsPDF MyChart, fileName, filePath
' As the object goes out of scope, the background instance of Word
' gets closed, if it wasn't open at the time of the creation of the object
Set oShapeExporter = Nothing
End Sub
To use the exporter object you have to paste the following code to a class module and name the class module cShapeExporter:
Option Explicit
' Storing the instance of Word in the object
Dim wdApp As Object
Dim wdDoc As Object
Dim wdWasOpen As Boolean
Private Sub Class_Initialize()
' Opening Word
If WordIsRunning Then
Set wdApp = GetObject(, "Word.Application")
wdWasOpen = True
Else
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdWasOpen = False
End If
' And creating a Document that will be used for the pasting and exporting
Set wdDoc = wdApp.Documents.Add
' Setting margins to 0 so we have no white borders!
' If you want, you can set custom white borders for the exported PDF here
With wdDoc.PageSetup
.LeftMargin = 0
.RightMargin = 0
.TopMargin = 0
.BottomMargin = 0
End With
End Sub
Private Sub Class_Terminate()
' Important: Close Word as the object is destroyed, but only if it wasn't
' previously opened!
If Not wdWasOpen Then
wdApp.Quit 0 '(wdDoNotSaveChanges)
Else
wdDoc.Close 0
End If
Set wdApp = Nothing
Set wdDoc = Nothing
End Sub
Public Sub ExportShapeAsPDF(xlShp As Object, fileName As String, filePath As String)
' Defining which objects can be exported, maybe others are also supported,
' they just need to support all the methods and have all the properties used
' in this sub
If TypeName(xlShp) = "ChartObject" Or TypeName(xlShp) = "Shape" Or TypeName(xlShp) = "ChartArea" Then
'fine
Else
MsgBox "Exporting Objects of type " & TypeName(xlShp) & " not supported, sorry."
Exit Sub
End If
' Copying the Excel object into the Word Document
xlShp.Copy
wdDoc.Range.Paste
Dim wdShp As Object
Set wdShp = wdDoc.Shapes(1)
' Resizing the Word Document
With wdDoc.PageSetup
.PageWidth = wdShp.Width
.PageHeight = wdShp.Height
End With
' Aligning the pasted object
wdShp.Top = 0
wdShp.Left = 0
' Export as .pdf
wdDoc.saveas2 fileName:=filePath & "\" & fileName, FileFormat:=17 '(wdExportFormatPDF)
' Delete shape in wdDoc
wdShp.Delete
End Sub
' Utility Function
Private Function WordIsRunning() As Boolean
Dim wdApp As Object
On Error Resume Next
Err.Clear
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
WordIsRunning = False
Else
WordIsRunning = True
End If
End Function
