Exporting a Range in PDF or XPS Formats in Excel 2010
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
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
Start Excel 2010.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.
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.
In the Projects pane, click Sheet1.
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
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.
While still in the Visual Basic Editor, press F8 to step through the code line-by-line and watch the effects in the worksheet.