Exporting an Excel File in Excel VBA

Similar to Word 2010, since Excel 2010 the Workbook object provides the method ExportAsFixedFormat(). This method allows exporting an Excel workbook as a PDF file or as an XPS file.

Example:

Sub ExportExcelToPDF()
    Dim appExcel As Excel.Application
    Dim workbook As Excel.Workbook
    Dim folderPath As String
    Dim pdfFileName As String
    Set appExcel = CreateObject("Excel.Application")
    folderPath = ThisWorkbook.Path & "\Export"
    ' Open the Excel workbook from the Export folder
    Set workbook = appExcel.Workbooks.Add(folderPath & "\MappeTest01.xlsx")
    ' Define the PDF filename to be created
    pdfFileName = folderPath & "\MappeTest01.pdf"
    ' Export the workbook as PDF
    workbook.ExportAsFixedFormat _
        Type:=xlTypePDF, Filename:=pdfFileName
    ' Close workbook without saving changes
    workbook.Close SaveChanges:=False
    ' Quit Excel application and clean up
    appExcel.Quit
    Set workbook = Nothing
    Set appExcel = Nothing
End Sub

Explanation:

• The Excel file is accessed similarly to Word by using the CreateObject() method, which returns a reference to an Excel.Application object.
• The parameter names differ slightly compared to Word:
  • The filename of the output file is passed via Filename (note capitalization).
  • The output format is specified with the Type parameter, which can be either xlTypePDF or xlTypeXPS.
• When closing the workbook with the Close() method, passing False for SaveChanges prevents saving any changes to the original file.
• The Excel application is then terminated with Quit().