Search code examples
vbapdfprintingformattingoutline

VBA Export to PDF losing outlines

When I'm using VBA to export to PDF I am losing some of the formatting. Image when printed Same section exported

I am trying to use VBA to export a selected group of tabs to PDF. If I use the below code it works and the formatting is perfect but I have to use the pop up box to save the file location:

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=iTotal, Copies:=1, _
Preview:=False, ActivePrinter:="Microsoft Print to PDF", Collate:=True, _
PrToFileName:=sPath & sFile, IgnorePrintAreas:=False

Instead I'd like to Export to PDF using the below code as this just saves the file to the specified path without me having to interact.

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=sPath & sFile, Quality:=xlQualityMaximum, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

The problem is that if I use the export code the formatting isn't quite right. Some of the outlines appear to be missing so there are gaps around the edges.

Does anyone know why it formats differently using export rather than print?

Solution

  • .PrintOut only shows the dialog, if you provide an empty string for PrToFileName, or if you omit the PrToFileName argument.

    Both code demonstrations work on my machine without showing the dialog asking for pdf file name, if I provide a valid file name.