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 & Application.PathSeparator
' 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, filePath, fileName
' 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 into 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
Private Sub Class_Initialize()
' Opening Word
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
' 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 instance as the object is destroyed.
wdApp.Quit 0 '(0 = wdDoNotSaveChanges)
Set wdApp = Nothing
Set wdDoc = Nothing
End Sub
Public Sub ExportShapeAsPDF(xlShp As Object, _
filePath As String, _
Optional ByVal fileName 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 '(17 = wdExportFormatPDF)
' Delete shape in wdDoc
wdShp.Delete
End Sub
