PDA

View Full Version : VBA Print area problem



emilholten
08-20-2018, 04:47 AM
Dear reader


I have a problem at my job with a VBA code for printing the spreadsheet as a pdf file.

The problem is that the printed pdf moves text, pictures etc. upwards (see attached photos - I could for some reason not attach the pdf files themselves??)
It continues on the following pages that are not shown in the pictures.
- It seems to me that the right margin (and maybe even page size) is not working properly, thus making the text push upwards.


To complicate it a little further the "problem" does not occur on my computer or any I have tried, but only on some of my customers (therefore the "working" and "not working" photos).

I use excel 2016 and have some suspicion that the problem occurs due to the customer using excel 2013, though it is only a hunk (and I have no possibility to check it right now).

The VBA code for compiling in PDF is (I have marked the may be part of the problem in red)


Sub PrintRange()
Dim sheetname As String
tabname1 = ActiveSheet.Name
Range("printindsats") = tabname1
Application.ScreenUpdating = False
Sheets("Rapport").Range("print_report").Value = ActiveSheet.Range("printomr").Value


Sheets("Rapport").Visible = True
Worksheets("Rapport").Activate


Run "Excel_ExportPDF"


MsgBox "En pdf-fil med resultaterne er gemt i samme folder som modellen - og med samme navn som modellen. Hvis du vil undgå at pdf-filen overskrives, skal du derfor omdøbe pdf-filen."
'Følgende linje skjuler report fanebladet (aktiver til slut, men ikke nødvendigt nu)
'Sheets("Report").Visible = False
Worksheets(tabname1).Activate
Application.ScreenUpdating = True


End Sub


Sub Excel_ExportPDF()
'PURPOSE: Generate A PDF Document With Selected Worksheet
'NOTES: PDF Will Be Saved To Same Folder As Excel File
'makroen udskriver hele printområdet i arket til pdf'en. Så det skal defineres i filen så det passer med hvad der skal skrives ud i sidste ende.




Dim CurrentFolder As String
Dim FileName As String
Dim myPath As String
Dim UniqueName As Boolean
Dim navn As String




UniqueName = False


'Store Information About Excel File
myPath = ActiveWorkbook.FullName
navn = ActiveSheet.Range("name_pdf")
CurrentFolder = ActiveWorkbook.Path & ""
FileName = Mid(myPath, InStrRev(myPath, "") + 1, _
InStrRev(myPath, ".") - InStrRev(myPath, "") - 1)

'Her defineres det område der skal udskrives i pdf-rapporten
ActiveSheet.PageSetup.PrintArea = "$C$3:$Q$623"
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftMargin = Application.CentimetersToPoints(0)
.RightMargin = Application.CentimetersToPoints(0)
.TopMargin = Application.CentimetersToPoints(0)
.BottomMargin = Application.CentimetersToPoints(0)
.HeaderMargin = Application.CentimetersToPoints(0)
.FooterMargin = Application.CentimetersToPoints(0)
.PaperSize = xlPaperA4
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 10
End With
'Save As PDF Document
On Error GoTo ProblemSaving
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=CurrentFolder & FileName & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
On Error GoTo 0


'Disable Page Breaks
ActiveSheet.DisplayPageBreaks = False
ActiveSheet.Select


Exit Sub


'Error Handlers
ProblemSaving:
MsgBox "There was a problem saving your PDF. This is most commonly" & _
" caused by the original PDF file already being open."
Exit Sub




End Sub



Do you have a good guess on why it does not work?


Best regards
Emil

emilholten
08-20-2018, 05:29 AM
It seems like the issue is the page size is different on the two. Not sure you can see that on the pictures.

The pages on the left picture is a little bit bigger than the pages on the right picture.