Excel – How to print to PDF with Excel VBA

microsoft excelmicrosoft-excel-2010pdfvba

As the title suggests… I'm looking to expand my automating to include directly printing to PDF. The macro(s) I have written already formats the print area and page setup as I need it. But when I go to record my macros nothing for printing, changing printers or saving to file is captured.

Is there a way to accomplish this? I could even get by if it just required a save location prompt. The file name I would imagine could be auto-generated by grabbing existing info within the spreadsheet.

Best Answer

if you are using Excel 2010 and above, then Excel provides you with in internal add-in which allows you to save the file as PDF format.
Use the following code in your macro to save your file as PDF

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\46506090\Desktop\Book1.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

Here you can replace the path C:\Users\46506090\Desktop\Book1.pdf and file name to your liking.

Edit: If you intend to publish the entire workbook as PDF and not just the sheet you are working on just replace ActiveSheet.ExportAsFixedFormat to ActiveWorkbook.ExportAsFixedFormat

Related Solutions

Excel VBA: How to use a unique username or save location for an export pdf macro

The full path to the root User folder of the currently logged-in user can be retrieved with:

Environ("USERPROFILE")

To save the file to the user's desktop, like you've done, you would use:

Environ("USERPROFILE") & "\Desktop\" & ...

For your specific code as provided

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Environ("USERPROFILE") & "\Desktop\" & Right(Range("D11"), 8) & "_ALL_SALES.pdf", _
     Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

Excel doesn’t print all active sheets to PDF

I have used your example file to successfully duplicate the problem and I did find a solution.

The problem with this spreadsheet is that you have mixed orientations : Some sheets have the Landscape orientation while others are in Portrait. While printing, Excel forces you to specify one orientation and this does not work, for two reasons:

Printing in Landscape Orientation will print all the selected sheets, but everything will be in Landscape Orientation, even the sheets marked as Portrait.
Printing in Portrait Orientation will stop upon reaching sheet "Sheet2 (6)" which is too wide to print in Portrait orientation. Reducing the column-widths in "Sheet2 (6)" can let it print in Portrait Orientation, except that some columns will be truncated.

Excel simply does not let you print in mixed orientation, which I believe is an oversight of its developers, not adding an option for this case.

The way to generate a PDF file with variable Orientation is not to use a PDF printer. Use instead the ribbon File > Save As and save the selected sheets in PDF format. This will save the sheets, each with its own correct orientation.

Best Answer

Related Solutions

Excel VBA: How to use a unique username or save location for an export pdf macro

Excel doesn’t print all active sheets to PDF

Related Question