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
Leave feedback about this