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.Convert Excel to PDFVisual Basic123456789101112131415161718'--------------------------------------------------------------------------------'Code by firstname.lastname@example.org (or) kumarapush777 (Fiverr)'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes'--------------------------------------------------------------------------------Public Sub Convert_Excel_To_PDF()Dim iSh As Worksheet, pdfFileName As StringSet iSh = ThisWorkbook.Sheets(1)'PDF File name to Save this Document aspdfFileName = ThisWorkbook.Path & "\PDF_File_" & VBA.Format(VBA.Now, "dd_mmm_yyyy") & ".pdf"'Excel Saveas PDF File nameiSh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, _Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True'Process CompletedMsgBox "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
Assume the records in sheet1 in range A1:K20 has to be printed in Landscape format along with a background image.Visual Basic12345678910111213141516171819202122Option Explicit'Visit https://officetricks.com for more Excel Tips & TricksSub Excel_VBA_Print()'Set Print AreaThisWorkbook.Sheets("Sheet1").PageSetup.PrintArea = "A1:K20"'Page Layout'ThisWorkbook.Sheets("Sheet1").PageSetup.Orientation = xlPortraitThisWorkbook.Sheets("Sheet1").PageSetup.Orientation = xlLandscape'Set Background Image or Watermark ImageThisWorkbook.Sheets("Sheet1").SetBackgroundPicture Filename:="D:\PrintBackgroundImage.JPG"'Enable a Label object to appear in print - Printing Label in ExcelLabel1.SelectWith Selection.PrintObject = FalseEnd With'Print 2 Copies of the pageThisWorkbook.Sheets("Sheet1").PrintOut Copies:=2, From:=1, To:=1End 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.
Once these settings are completed, press Ctrl + F2 to view Print Preview. If the preview looks good, press Ctrl + P to send the printer to default Printer.
Excel VBA Print Labels
This above code also explains how to print labels from excel. First you have to make the vba code to select the label object and modify its “Printobject” property. By using the above code, printing a excel sheet can be assigned to a command button or any scheduled printing can also be done.
Also Read: Print Yearly Calendar for Quick Reference
People generate dashboards, pivot tables/charts, repots in Excel sheets for their business and personal uses and few times print them too. But, it is advised that you verify code logic twice before placing this print login inside a loop. If the loop goes endless, then there are chances that your printer will end up in too many pages, wasting lot of paper.
Convert Excel To PDF – VBA Code Print Selected Area