Forum Discussion

morrish202's avatar
morrish202
Copper Contributor
Apr 20, 2024

Borderless Print to PDF

I want to export an excel sheet to a PDF. I do so with the following VBA code (the comments are just some things I tried):

ThisWorkbook.Sheets(sheet).Select
    
    'ActiveSheet.PageSetup.PrintArea = "A1:A320"
    With ActiveSheet.PageSetup
        '.Zoom = False
        '.FitToPagesTall = 1
        '.FitToPagesWide = 1
        '.LeftMargin = Application.InchesToPoints(0)
        '.RightMargin = Application.InchesToPoints(0)
        '.TopMargin = Application.InchesToPoints(0)
        '.BottomMargin = Application.InchesToPoints(0)
    End With
    
    Application.ActivePrinter = "Microsoft Print to PDF on Ne00:"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filepath & "_noFont.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

My problem is that the pdf has a border at the bottom and on the left side regardeless of my margin-settings.

Printed as PDF

 

Thanks in advance and let me know if you need more information.

 

System details:

  • Excel 2019 Version 2312 (Build 17126.20132) 64-bit

I’ve tried:

  • Set Custom Margins to 0
  • Use Microsoft Print to PDF as Printer: Application.ActivePrinter = "Microsoft Print to PDF on Ne00:"
  • Specifically define PrintArea: ActiveSheet.PageSetup.PrintArea = "A1:A64"
  • Define Scaling as fot to: 1 page wide by 1 page tall: .FitToPagesTall = 1 .FitToPagesWide = 1
  • Disable “Scale Content for A4 paper sizes” (Options\Advanced\General)
  • Check default paper size: A4 with limits set to 0,00 cm
excel
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 21, 2024

    morrish202 

    To export an Excel sheet to a PDF without borders, you need to adjust the print settings and page setup options appropriately. Here is a revised version of your VBA code that should help achieve a borderless PDF output:

    Vba code is untested, please backup your file.

    Sub ExportToPDF()
    Dim ws As Worksheet
    Dim filepath As String

    ' Define the worksheet to be exported
    Set ws = ThisWorkbook.Sheets("YourSheetName")
    
    ' Define the file path for the PDF
    filepath = "C:\Your\FilePath\Here\YourFileName.pdf"
    
    ' Unprotect the worksheet if it's protected
    ws.Unprotect
    
    ' Set print settings to borderless
    With ws.PageSetup
        .LeftMargin = 0
        .RightMargin = 0
        .TopMargin = 0
        .BottomMargin = 0
        .HeaderMargin = 0
        .FooterMargin = 0
        .CenterHorizontally = True
        .CenterVertically = True
    End With

    ' Set the active printer to Microsoft Print to PDF
    Application.ActivePrinter = "Microsoft Print to PDF on Ne00:"
    
    ' Export the sheet as PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filepath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
    ' Protect the worksheet again if needed
    ws.Protect
End Sub

    Make sure to replace "YourSheetName" with the name of your sheet and specify the correct file path for the PDF in the "filepath" variable.

    This code sets the print margins to 0 to achieve a borderless output. It also centers the content both horizontally and vertically on the page. Additionally, it ensures that the worksheet is unprotected before changing the page setup options and then protects it again after exporting the PDF. The text, steps and code was create with the help of AI.

    It is not possible to predict whether all of this is correct and works, as there are several influences that could potentially mess up a lot of these plans. Influences such as printers, printer drivers, sheet protection, etc., up to the font. That's why my answer are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources