Convert Excel To PDF – VBA Code Print Selected Area

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 https://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

Assume the records in sheet1 in range A1:K20 has to be printed in Landscape format along with a background image.

Option Explicit
'Visit https://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.

1. 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.
2. Portrait or Landscape – This is also present in Menu ->Page Layout -> Orientation.
3. Misc Options: There are also options available to insert a header/footer, background image, Margin, Page or Line break etc.
4. 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.