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.
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.
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
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
Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.
0 comments:
Post a Comment