Excel VBA Print & Save as PDF Commands
This article provides code for creating PDF from Excel & Vba print macro Excel 2010 or versions less than that. Just like printing a PDF, Word document the Excel reports, dashboards; charts are also sent to printer to have a hard copy. So, let’s learn what the available methods to print an Excel are and also how to automate a print process. In many shops, the invoices, purchase bill, inventory details, balance sheet are all maintained in Excel. So, it becomes important for them to print or create PDF as the final version for future hard copy records.Convert Excel to PDF using VBA Code
Use this Excel VBA save as PDF code to convert the Excel to PDF document. Before this step, you can also define Print area that is explained in the next code in this page.'--------------------------------------------------------------------------------
'Code by author@officetricks.com (or) kumarapush777 (Fiverr)
'Visit http://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Public Sub Convert_Excel_To_PDF()
Dim iSh As Worksheet, pdfFileName As String
Set iSh = ThisWorkbook.Sheets(1)
'PDF File name to Save this Document as
pdfFileName = ThisWorkbook.Path & "\PDF_File_" & VBA.Format(VBA.Now, "dd_mmm_yyyy") & ".pdf"
'Excel Saveas PDF File name
iSh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'Process Completed
MsgBox "Process Completed"
End Sub
Worksheet.ExportAsFixedFormat is the command used here which can also be used for converting the Excel to other formats. This code will save Excel as PDF and also open the document once the conversion is completed. Cool, isn't it.
Macro: Excel VBA Print Out Selected Area
In Excel macro we have options to print the content through Workbook, Window, Sheet, Range, Chart classes using the below function. Function PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName]) But, before issuing this vba print command, the basic steps has to be taken care as well. Otherwise we might end up in printing unwanted data, wasting paper.- Set print area,
- Page orientation - Portrait or Landscape
- Set Excel Watermark image for Print
- Printing Labels
Option Explicit
'Visit http://officetricks.com for more Excel Tips & Tricks
Sub Excel_VBA_Print()
'Set Print Area
ThisWorkbook.Sheets("Sheet1").PageSetup.PrintArea = "A1:K20"
'Page Layout
'ThisWorkbook.Sheets("Sheet1").PageSetup.Orientation = xlPortrait
ThisWorkbook.Sheets("Sheet1").PageSetup.Orientation = xlLandscape
'Set Background Image or Watermark Image
ThisWorkbook.Sheets("Sheet1").SetBackgroundPicture Filename:="D:\PrintBackgroundImage.JPG"
'Enable a Label object to appear in print - Printing Label in Excel
Label1.Select
With Selection
.PrintObject = False
End With
'Print 2 Copies of the page
ThisWorkbook.Sheets("Sheet1").PrintOut Copies:=2, From:=1, To:=1
End Sub
Ensure there is some data in excel before executing the code(F5 - to run the macro). Once it is done, Excel will send the selected Print area to the default printer.
Manual: Excel Set Print Area & Page Layout
In Excel before printing a worksheet, the following basic steps have to be taken care.- Set Print area – Choose which cells have to appear in the paper after a print. This option will be available in Menu ->Page Layout -> Print Area. Select the cells that you want to print and then choose this option.
- Portrait or Landscape – This is also present in Menu ->Page Layout -> Orientation.
- Misc Options: There are also options available to insert a header/footer, background image, Margin, Page or Line break etc.
- In case if there is a need for more controls, click on Print Titles button available in Page Layout Menu.