Share via

Exporting a Range in PDF or XPS Formats in Excel 2010

  • Artículo

Pancarta de nota rápida de Office

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.

Última modificación: jueves, 05 de mayo de 2011

Hace referencia a: Excel 2010 | Office 2010 | VBA

En este artículo
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Applies to:    Microsoft Excel 2010

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:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

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.

    Nota

    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:
  ' https://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

Test the Solution

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.

Next Steps