Determine PageSetup Zoom Value When Fit To is Selected and related issues

[Gav42]

I am trying to determine (xl 2010) what the PageSetup zoom value is when FitToPagesWide is set. I found this 2006 post that describes the problem and a solution that still works. The following code works, thanks to Ron Page but surely there is a better way than using an Excel4 macro?

with sh.pagesetup
        .PaperSize = xlPaperA4
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .FitToPagesTall = False
        'Application.PrintCommunication = False
        [COLOR=#EF2929]Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})"[/color][COLOR=#CC0000][/color]       
       ' MsgBox "Zoom factor is " & ActiveSheet.PageSetup.Zoom
        Select Case .Zoom  
        Case Is > 100
            .Zoom = 100
        Case Is < 69
            .Orientation = xlLandscape
            .FitToPagesWide = 1
        End Select

[URL unfurl="true"]http://www.ozgrid.com/forum/showthread.php?t=49644[/url]

There is a second problem that is what prompted me to write this code. FitToPagesWide seems to zoom some pages unnecessarily. It can return a zoom of 50-60% but in fact if I do the job by hand I find that 71% is fine. So if there is a workaround for that issue I would love to hear (I have reset the last cell and defined a print range but niether helps).

Finally is there a way of telling how many pages wide the print will be? I am thinking I could create my own (inefficient) function to loop though different zoom values to do a fit to 1 wide.

Thanks for your help.

Gav

 

[combo]

You can play with VPageBreaks collection.
Alternative to .FitToPagesWide=1:
sh.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

combo