I am trying to discover a way to optimise the .PageSetup properties of a
sheet (in VBA) so the user can simply click PrintPreview or Print... without
needing to adjust any properties to make the printed report readable.

The sheet concerned can have a variable amount of data and I want to
implement the following rules. Starting with .Orientation=xlLandscape...
a) if it fits on a single page leave it as is,
b) if the page appears to be narrow and long switch to xlPortrait to
maximise use of the page,
c) set a .Zoom % to achieve a 1 page wide print job (and 1 or more pages
down) so long as the .Zoom percentage does not go below (say) 50% (ie. avoid
an unreadable "greeked" result).

Has anyone been here before (in Excel 2K)? NB the .UsedRange property is
viable on this sheet!

Cheers,
Frank.

Frank,
One way would be to apply your initial conditions, then check the
..HPageBreaks.Count & .VPageBreaks.Count properties to see how Excel will
deal with your combination of data, paper size and printer limitations.
If this yields unsuitable results, apply your rules below under the desired
result is achieved.

NickHK

"Frank_Hamersley" wrote in message
...
I am trying to discover a way to optimise the .PageSetup properties of a
sheet (in VBA) so the user can simply click PrintPreview or Print...
without
needing to adjust any properties to make the printed report readable.

The sheet concerned can have a variable amount of data and I want to
implement the following rules. Starting with .Orientation=xlLandscape...
a) if it fits on a single page leave it as is,
b) if the page appears to be narrow and long switch to xlPortrait to
maximise use of the page,
c) set a .Zoom % to achieve a 1 page wide print job (and 1 or more pages
down) so long as the .Zoom percentage does not go below (say) 50% (ie.
avoid
an unreadable "greeked" result).

Has anyone been here before (in Excel 2K)? NB the .UsedRange property is
viable on this sheet!

Cheers,
Frank.

Hi Frank
This works reasonably well for me. Overdoes things a bit for small
tables. Operates on the UsedRange of the ActiveSheet.

'Simplified version of a function written by Francois Van Wauwe,
Microsoft.Public.Excel.Programming, 25th Sept 1998

Function Set_My_Used_Print_Area()
Dim MyZoom As Integer
Dim UsedWidth As Variant, UsedHeight As Variant
Dim IsLandScape As Boolean
Dim pw As Double, ph As Double
Dim ZoomWidth As Integer, ZoomHeight As Integer
With ActiveSheet.UsedRange
UsedWidth = .Width
UsedHeight = .Height
End With
If UsedWidth UsedHeight Then
' Landscape
IsLandScape = True
pw = Application.CentimetersToPoints(24.7)
ph = Application.CentimetersToPoints(16.6)
With ActiveSheet.PageSetup
.TopMargin = Application.CentimetersToPoints(1)
.BottomMargin = Application.CentimetersToPoints(1)
.LeftMargin = Application.CentimetersToPoints(1.5)
.RightMargin = Application.CentimetersToPoints(1.5)
End With
Else
' Portrait
IsLandScape = False
pw = Application.CentimetersToPoints(16.6)
ph = Application.CentimetersToPoints(24.7)
With ActiveSheet.PageSetup
.LeftMargin = Application.CentimetersToPoints(1)
.RightMargin = Application.CentimetersToPoints(1)
.TopMargin = Application.CentimetersToPoints(1.5)
.BottomMargin = Application.CentimetersToPoints(1.5)
End With
End If

ZoomWidth = Int(pw / UsedWidth * 100)
ZoomHeight = Int(ph / UsedHeight * 100)
If ZoomWidth < ZoomHeight Then 'smallest of the two
MyZoom = ZoomWidth
Else
MyZoom = ZoomHeight
End If
With Application.ActiveSheet.PageSetup
If (MyZoom <= 400) And (MyZoom = 10) Then
.Zoom = MyZoom
Else
.Zoom = 100
End If
If IsLandScape Then
.Orientation = xlLandscape
Else
.Orientation = xlPortrait
End If
End With
End Function


regards
Paul
"Frank_Hamersley" wrote in message ...
I am trying to discover a way to optimise the .PageSetup properties of a
sheet (in VBA) so the user can simply click PrintPreview or Print... without
needing to adjust any properties to make the printed report readable.

The sheet concerned can have a variable amount of data and I want to
implement the following rules. Starting with .Orientation=xlLandscape...
a) if it fits on a single page leave it as is,
b) if the page appears to be narrow and long switch to xlPortrait to
maximise use of the page,
c) set a .Zoom % to achieve a 1 page wide print job (and 1 or more pages
down) so long as the .Zoom percentage does not go below (say) 50% (ie. avoid
an unreadable "greeked" result).

Has anyone been here before (in Excel 2K)? NB the .UsedRange property is
viable on this sheet!

Cheers,
Frank.

"NickHK" wrote
One way would be to apply your initial conditions, then check the
.HPageBreaks.Count & .VPageBreaks.Count properties to see how Excel will
deal with your combination of data, paper size and printer limitations.

Will check this out especially as it will respond to the currently selected
printer imaging capabilities, although I must admit I like the look of
Paul's approach.

Many thanks, Frank.

"Paul Robinson" wrote
This works reasonably well for me. Overdoes things a bit for small
tables. Operates on the UsedRange of the ActiveSheet.

'Simplified version of a function written by Francois Van Wauwe,
Microsoft.Public.Excel.Programming, 25th Sept 1998

This is nice and deterministic. Many thanks!

Frank.

Hi
Re-reading your original mail, this function only fits to 1 page, so
isn't quite what you wanted. Should be adaptable though?
cheers
Paul

"Frank_Hamersley" wrote in message ...
"Paul Robinson" wrote
This works reasonably well for me. Overdoes things a bit for small
tables. Operates on the UsedRange of the ActiveSheet.

'Simplified version of a function written by Francois Van Wauwe,
Microsoft.Public.Excel.Programming, 25th Sept 1998

This is nice and deterministic. Many thanks!

Frank.

"Paul Robinson" wrote
Re-reading your original mail, this function only fits to 1 page, so
isn't quite what you wanted. Should be adaptable though?

Certainly was - and works very nicely.

Whilst I was working on this it occured to me that Excel doesn't handle
wide+shallow or narrow+deep reports in a very environmentally friendly way.

What it needs is something like multiple pages on a single akin to Word
multi column pages - ie. basically to optimise/minimise the amount of white
space on the page.

Given the number of pages imaged today and everyday even a simple
improvement might save a lot of trees!

Cheers, Frank.

"Paul Robinson" wrote
Should be adaptable though?

Paul,

Have found a small bug in your function that you might like review.

It relates the ActiveSheet.UsedRange.Width property returning the number of
Points to the top left of the last cell in the range - ie. it does not
include the width of this last column (or height of the last row). The
mod'ed code now looks like ....

' Get the visible range in points - ensuring it rounds up
With ActiveSheet.UsedRange
lngUsedWidth = CLng(.Width + .Columns(.Columns.Count).Width + 1#)
lngUsedHeight = CLng(.Height + .Rows(.Rows.Count).Height + 1#)
End With

I suspect this will work even if the last column or row is hidden because
Excel reports the Width or Height of such columns/rows as 0 points.

As Agent 86 would have said its the "old top left corner mentality yet
again"! Not a very intuitive arrangement although it is consistent - and
too late to change Excel now.

Regards,
Frank.