Wednesday 25 February 2015

Generate PDF document from Excel - VBA

Welcome to Logically Proven blog.

This post demonstrates how to generate a PDF document from Excel using VBA (Visual Basic for Applications).

Sometimes it is required to generate a PDF document from the Excel workbook or worksheet. This post gives you a basic idea how to generate the PDF document from a workbook or a worksheet.

We have to follow these two points in  order to generate a PDF document.

1. Page set up
2. Export as PDF

Page set up:

It is very important to set up the page of a worksheet before exporting as a PDF document.
The following line will set the paper size of a worksheet to A4.

    'set worksheet paper size to A4
    'Report is the worksheet name
    Report.PageSetup.PaperSize = xlPaperA4

If you want to see the available paper sizes please follow this link -
https://msdn.microsoft.com/en-us/library/office/ff834612.aspx

If you want to set the paper size of a complete workbook iterate through each worksheet in a workbook.

Export as PDF:

The second step is exporting the worksheet/workbook as PDF document. The following example exports the "Report" worksheet to PDF document.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Sub GeneratePDF()
    
    'Declare variables
    Dim strFileName As String
    Dim ReportNum As Long
    Dim strPath As String
    Dim strFilePath As String
    
    'error handling
    On Error GoTo err_handler
            
    'Set report name
    strFileName = "ReportName"
    
    'Save the pdf document on desktop
    'set the path if you want to save the pdf document in a different folder
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
        
    'full path
    strFilePath = strPath & strFileName
    
    'set worksheet paper size to A4
    'Report is the worksheet name
    Report.PageSetup.PaperSize = xlPaperA4
    
    'Replace Report with ThisWorkbook if you want to export the complete workbook
    
    'Export the sheet Report in pdf
    Report.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strFilePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
       
    Exit Sub
            
    
err_handler:
    MsgBox Err.Description, vbOKOnly, "Error"
           
End Sub

In line 17: we are getting the user's desktop path. This line works only if have a reference to the "Microsoft Shell Controls and Automation" library. Please add a reference to this library.

For adding, in code window
Select Tools -> Select References -> Search for the library in the list -> Check the button "Microsoft Shell Controls and Automation" -> Press OK.

In line 29: This code exports only one worksheet. If you want to export the entire workbook, replace the Report in line 29 with ThisWorkbook.

In line 30: We are defining the type of document to export. In our case PDF.

In line 31: Mention full path. you can give directly the file path including file name in double-quotes (e.g. "C:\ReportName").

In line 32: Mention the quality of the document. (e.g. images) The other available quality is xlQualityMinimum if you reduce the memory size of the exported document (e.g. if your report has more images).

In line 35: If you change the value to "True", the PDF document opens immediately after generating.

This way you can export the workbook/worksheet to PDF.


Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.

0 comments:

Post a Comment

 
biz.