How to setup page correctly for pdf export using vba?

I am trying to export a specific range on a worksheet as a PDF. I want a 0.5cm margin to the left and right. Apart from that, I want the width of the range to fill the rest of the width of the page and I also want to center the printed range vertically on the page. Unfortunately, there seems to be a problem with my calculations that is not exactly obvious to me. I suspect there are some settings that are not included in my calculation, but where to find them is a mystery to me.

Function ExportToPDF() As String

    Dim ws As Worksheet
    Dim print_range As Range
    Dim pdf_filepath As String
    Dim days_in_month As Integer
    Dim print_range_width As Double
    Dim print_range_height As Double
    Dim page_width As Double
    Dim page_height As Double
    Dim cm_to_points As Double
    Dim zoom_percentage As Integer
    Dim horizontal_margins As Double
    Dim vertical_margins As Double
    
    ws_period = ThisWorkbook.ActiveSheet.Cells(6, 2).Value
    days_in_month = Day(DateAdd("m", 1, ws_period) - 1)
    Set ws = ThisWorkbook.ActiveSheet
    Set print_range = ws.Range(ws.Cells(1, 1), ws.Cells(days_in_month + 13, 9)) '13 fixed rows independent of the month
    print_range_width = print_range.Width
    print_range_height = print_range.Height
    cm_to_points = 72 / 2.54
    page_width = 21 * cm_to_points
    page_height = 29.7 * cm_to_points
    
    zoom_percentage = Fix(((page_width * 0.9575) / (print_range_width + 1 * cm_to_points)) * 100)
    horizontal_margins = Round((page_width * 0.9575 - print_range_width * (zoom_percentage / 100)) / 2, 1)
    vertical_margins = Round((page_height * 0.9475 - print_range_height * (zoom_percentage / 100)) / 2, 1)
    
    With ws.PageSetup
        .PaperSize = xlPaperA4
        .FooterMargin = 0
        .HeaderMargin = 0
        .Orientation = xlPortrait
        .Zoom = zoom_percentage
        .LeftMargin = horizontal_margins
        .RightMargin = horizontal_margins
        .BottomMargin = vertical_margins
        .TopMargin = vertical_margins
    End With
    pdf_filepath = ThisWorkbook.Path & "\Export" & MonthName(month(ws_period)) & " " & year(ws_period) & ".pdf"
    print_range.ExportAsFixedFormat Type:=xlTypePDF, _
                            Filename:=pdf_filepath, _
                            Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, _
                            OpenAfterPublish:=False  
    ExportToPDF = pdf_filepath
End Function

Using the factors 0.9575 and 0.9475, I have now manually adjusted the export by trial and error so that it looks somewhat right. However, it should actually work without these factors. Unfortunately, I can’t use FitToPageWidth and FitToPageHeight because there is other content on my worksheet.

Any ideas how to fix this?

You need to sign in to view this answers

Related Post