This documentation is archived and is not being maintained.

Exporting a Range in PDF or XPS Formats in Excel 2010

Office 2010

Office Quick Note banner

Handy Programming Tips for Microsoft Excel 2010: Learn how to export a range of data that is in a Microsoft Excel 2010 workbook to a PDF or an XPS file format.

Last modified: May 05, 2011

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

Excel 2010 has file format converters that import and export data to different file formats such as PDF and XPS. In this topic, you programmatically create a range of data and then export that data to a PDF or an XPS file format. To complete this task, you must do the following:

In this task, you add programming code that enables you to export worksheet data to a PDF or an XPS file format.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub TestExportAsFixedFormat()
      ' For information about the final parameter, see this page:
      ' http://msdn.microsoft.com/en-us/library/aa338206.aspx
     
      Dim rng As Range
      Set rng = Range("A1:E10")
      SetupRangeData rng
     
      Dim fileName As String
      ' Change this file name to meet your own needs:
      fileName = "C:\Temp\Export.pdf"
     
      ' Many of these properties are optional, and are included
      ' here only to demonstrate how you might use them. The
      ' Type parameter can be one of xlTypePDF and xlTypeXLS;
      ' the Quality parameter can be one of xlQualityStandard and
      ' xlQualityMinimum. Setting the OpenAfterPublish property
      ' to True fails if you do not have a default viewer
      ' installed and configured.
     
      rng.ExportAsFixedFormat Type:=xlTypePDF, _
       fileName:=fileName, Quality:=xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=True, _
       From:=1, To:=1, OpenAfterPublish:=True
    End Sub
    
    Sub SetupRangeData(rng As Range)
      rng.Formula = "=RANDBETWEEN(1, 100)"
    End Sub
    
    

In this task, you step through the VBA code that creates a range of values and then enables you to export the data in a PDF or an XPS fixed file format.

To run the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. While still in the Visual Basic Editor, press F8 to step through the code line-by-line and watch the effects in the worksheet.

Show: