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
.pagesetupobject. But one thing that has an effect as well, is the actual printer used. Changing the printer after setting up a sheet can (and often will) change the page breaks, margins etc. As such you should always set up the page with whichever pdf' printer you will use in the end. What's more, if the printer has some 'default' margin, this cannot be set through VBA, unless the printer has an VBA API – CimonPDFCreatorhas aCOMinterface, where one can change many settings, and some other settings can be changed manually, and will have a global effect.CutePDFdoes not. The same goes forMicrosoft print to PDF. – Cimon