Excel.ExportAsFixedFormat Method in MS Excel in VB.NET

In this article I am going to explain about how to export a range of data in a Microsoft Excel 2010 worksheet to PDF or XPS format.
  • 7072

Introduction

In this article I am going to explain about how to export a range of data in a Microsoft Excel 2010 worksheet to PDF or XPS format. For this we use Excel.ExportAsFixedFormat Method. Using this method we can export a range of data in a Microsoft Excel 2010 worksheet to PDF or XPS format.

Microsoft Office 2010 offer some powerful tools, using this tools you can create application. Using Microsoft Visual Basic for Applications (VBA) you can create your own application according to your need. These application can performer some specific task.

For creating application we can use

  • VBA host of Excel 2010
  • VBA host of PowerPoint 2010
  • VBA host of Word 2010

NOTE : OneNote 2010 is not a VBA host.

Code that we use in this application are given below

Sub TestExportAsFixedFormat()

    ' For information on the final parameter, see this page:
    
http://msdn.microsoft.com/en-us/library/aa338206.aspx 
    
    Dim
 rng As Rang
    rng = Range("A1:E10")
    SetupRangeData(rng)
    Dim fileName As 
String
    ' Change this file name to meet your own needs:
    
    fileName = 
"D:\vipendra\msdn\excel\e11\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 will fail if you don't 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

Step for creating Application

Step 1 : Start MS Excel 2010 :

p.jpg

p (1).jpg
 

Step 2 : Using Alt + F11 Key Start Visual Basic for Applications (VBA) Window :

p (2).jpg
 

Step 3 : Select on Sheet 1 :

p (3).jpg
 

Step 4 : Right Click on Sheet 1 and select View Code :

p (4).jpg

p (5).jpg
 

Step 5 : Write Code in Visual Basic for Applications (VBA) Window :

Clipboard06.jpg

Step 6 : Run Application using F5 :

Step 7 : Macros window will open, Select Macros name and click on Run Button :

Clipboard07.jpg
 

Step 8 : Output of Application :

Clipboard08.jpg

Data In Excel Sheet 
 

Clipboard021.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.