I'm using Excel 2016 with Microsoft office 365. I need to retreive the pagesetup.zoom value for a area fitted into one page wide by one page tall.
Some Introduction
Before printing a worksheet, I want to run a page setup macro that will adjust the print zoom value to make sure the printed page look the same no matter on wich computer or printer you run it and all page break are at the same place. On the worksheet, all my rows and colums are of the same hight and same width and canot be modified. Depanding on the printer's marging, somes times I ended up with the last row or column of the page on the next page.
I solve the problem by creating a macro that select on the worksheet the area of the first page too print and fit the print area to 1 page tall by one page wide. What I need to do at that point if to retreive the zoom value for that fitting. But at that point the pagesetup.zoom value return false since it is set to fit one page wide by one page tall.
If you do it manually in Excel and go back to the page setup wizard after tyou fitted the area of the first page, you can see the zoom value is there (but cannot be edited). By selecting the zoom print option, it retain the value and then you can reset the print area to the full worksheet. The following macro worked fine on many computer with different version of excel and different printer and my goal was achived. But I'm now working in a new office (new computer, new printer) and now the macro is not working. The only difference I can think of is office 360 vs 365. the pagesetup.zoom value get reset to 100% at some point.
Any Ideas?
Sub MiseEnPage()
Me.Select 'Me is a worksheet
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
With Me.PageSetup
.Zoom = 100
.PrintTitleRows = ""
.PrintArea = "$A$1:$AO$52"
End With
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
With Me.PageSetup
.PaperSize = 1
.Orientation = xlPortrait
.LeftMargin = Application.CentimetersToPoints(0.4)
.RightMargin = Application.CentimetersToPoints(0.4)
.TopMargin = Application.CentimetersToPoints(0.6)
.BottomMargin = Application.CentimetersToPoints(1.6)
.HeaderMargin = Application.CentimetersToPoints(0)
.FooterMargin = Application.CentimetersToPoints(0.7)
.PrintGridlines = False
.CenterHorizontally = True
.CenterVertically = False
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
At this point pagesetup.zoom=false
With Me.PageSetup
.FitToPagesWide = False
.FitToPagesTall = False
End With
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
At this point, before office 365 the pagesetup.zoom value was preserved to the fitted page (Ex: pagesetup.zoom=97%).
Now the value is reset to 100% at this point
With Me.PageSetup
.PrintTitleRows = "$1:$9"
.PrintArea = "$A:$AO"
End With
Application.PrintCommunication = True
DoEvents
End Sub
Some Introduction
Before printing a worksheet, I want to run a page setup macro that will adjust the print zoom value to make sure the printed page look the same no matter on wich computer or printer you run it and all page break are at the same place. On the worksheet, all my rows and colums are of the same hight and same width and canot be modified. Depanding on the printer's marging, somes times I ended up with the last row or column of the page on the next page.
I solve the problem by creating a macro that select on the worksheet the area of the first page too print and fit the print area to 1 page tall by one page wide. What I need to do at that point if to retreive the zoom value for that fitting. But at that point the pagesetup.zoom value return false since it is set to fit one page wide by one page tall.
If you do it manually in Excel and go back to the page setup wizard after tyou fitted the area of the first page, you can see the zoom value is there (but cannot be edited). By selecting the zoom print option, it retain the value and then you can reset the print area to the full worksheet. The following macro worked fine on many computer with different version of excel and different printer and my goal was achived. But I'm now working in a new office (new computer, new printer) and now the macro is not working. The only difference I can think of is office 360 vs 365. the pagesetup.zoom value get reset to 100% at some point.
Any Ideas?
Sub MiseEnPage()
Me.Select 'Me is a worksheet
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
With Me.PageSetup
.Zoom = 100
.PrintTitleRows = ""
.PrintArea = "$A$1:$AO$52"
End With
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
With Me.PageSetup
.PaperSize = 1
.Orientation = xlPortrait
.LeftMargin = Application.CentimetersToPoints(0.4)
.RightMargin = Application.CentimetersToPoints(0.4)
.TopMargin = Application.CentimetersToPoints(0.6)
.BottomMargin = Application.CentimetersToPoints(1.6)
.HeaderMargin = Application.CentimetersToPoints(0)
.FooterMargin = Application.CentimetersToPoints(0.7)
.PrintGridlines = False
.CenterHorizontally = True
.CenterVertically = False
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
At this point pagesetup.zoom=false
With Me.PageSetup
.FitToPagesWide = False
.FitToPagesTall = False
End With
Application.PrintCommunication = True
DoEvents
Application.PrintCommunication = False
At this point, before office 365 the pagesetup.zoom value was preserved to the fitted page (Ex: pagesetup.zoom=97%).
Now the value is reset to 100% at this point
With Me.PageSetup
.PrintTitleRows = "$1:$9"
.PrintArea = "$A:$AO"
End With
Application.PrintCommunication = True
DoEvents
End Sub