Page Setup from VBA prints half wide

I have six worksheets generated by VBA using data copied from eight
worksheets entered by hand, all in the same workbook. All specify 11 x 17
landscape (except one VBA generated sheet that is landscape), Fit to 1 page
wide with the radio button active, and Fit to 99 pages tall (I've tried false
to get blank for this field), etc. The page setup dialogs have the same data
in all fields.

I've printed (and used preview) to both LJ 5Si and LJ 8000 DN, both simplex
and duplex. I've tried reinstalling the printer. Both are network printers.

The manually entered sheets print the full width of the printable area,
about 16". The VBA generated sheets print and preview half the full width of
the printable area, about 8". The behavior is the same on letter size.
Manually changing page setup for the VBA generated sheets does not correct
the problem although using zoom does allow statically printing full width.
However, as the data changes, column widths will change and the zoom factors
will have to be manually changed; this is not acceptable.

I've tried computing the zoom factor from the width of the range, but the
result is too large and I have to fudge the zoom downward by 4-13% depending
upon the sheet. Obviously, this won't work unless I'm willing to reset the
fudge factors; I'm not.

Clearly, I'm missing something here. Any clue would be helpful.

With the help of a post by Tom Ogilvy, I've obtained a workaround as follows:

The workaround starts from the observation that using FitToPagesWide results
in printing half wide and that Excel computes the zoom corresponding to the
Fit in the PageSetup dialog box after a Page Preview. One Excel Programming
newsgroup query (HA) asked for code to obtain that value and I've included it
in the code snippet below. The value is then doubled and stored in the
VBA .Zoom parameter to print with a fixed zoom factor.

'Set the context for all . references to the worksheet's page setup
'ws is a worksheet object
With wsMaster.PageSetup
'Step 1: Set the parameters desired even though they only
'fit to half the page width.
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.Orientation = xlPortrait
.PaperSize = xlPaper11x17

'Step 2: Find out what zoom would be applied (solution from newsgroup)
'in order to calculate the Zoom %, a PrintPreview must initiated.
SendKeys "%C"
wsMaster.PrintPreview
'to get/set the Zoom %, initiate the Page Setup Dialog box.
SendKeys "P%A~"
Application.Dialogs(xlDialogPageSetup).Show
fZoom = ActiveSheet.PageSetup.Zoom

'Step 3: Since the fit is half wide with zoom false,
'set the fixed zoom to double the value from the dialog
'Might have to fudge this if the right border gets cut off.
.Zoom = 2 * fZoom

'Voila! the worksheet will fit the next time print preview is run.
End With



"Student" wrote:

> I have six worksheets generated by VBA using data copied from eight
> worksheets entered by hand, all in the same workbook. All specify 11 x 17
> landscape (except one VBA generated sheet that is landscape), Fit to 1 page
> wide with the radio button active, and Fit to 99 pages tall (I've tried false
> to get blank for this field), etc. The page setup dialogs have the same data
> in all fields.
>
> I've printed (and used preview) to both LJ 5Si and LJ 8000 DN, both simplex
> and duplex. I've tried reinstalling the printer. Both are network printers.
>
> The manually entered sheets print the full width of the printable area,
> about 16". The VBA generated sheets print and preview half the full width of
> the printable area, about 8". The behavior is the same on letter size.
> Manually changing page setup for the VBA generated sheets does not correct
> the problem although using zoom does allow statically printing full width.
> However, as the data changes, column widths will change and the zoom factors
> will have to be manually changed; this is not acceptable.
>
> I've tried computing the zoom factor from the width of the range, but the
> result is too large and I have to fudge the zoom downward by 4-13% depending
> upon the sheet. Obviously, this won't work unless I'm willing to reset the
> fudge factors; I'm not.
>
> Clearly, I'm missing something here. Any clue would be helpful.

With the help of a post by Tom Ogilvy, I've obtained a workaround as follows:

The workaround starts from the observation that using FitToPagesWide results
in printing half wide and that Excel computes the zoom corresponding to the
Fit in the PageSetup dialog box after a Page Preview. One Excel Programming
newsgroup query (HA) asked for code to obtain that value and I've included it
in the code snippet below. The value is then doubled and stored in the
VBA .Zoom parameter to print with a fixed zoom factor.

'Set the context for all . references to the worksheet's page setup
'ws is a worksheet object
With wsMaster.PageSetup
'Step 1: Set the parameters desired even though they only
'fit to half the page width.
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.Orientation = xlPortrait
.PaperSize = xlPaper11x17

'Step 2: Find out what zoom would be applied (solution from newsgroup)
'in order to calculate the Zoom %, a PrintPreview must initiated.
SendKeys "%C"
wsMaster.PrintPreview
'to get/set the Zoom %, initiate the Page Setup Dialog box.
SendKeys "P%A~"
Application.Dialogs(xlDialogPageSetup).Show
fZoom = ActiveSheet.PageSetup.Zoom

'Step 3: Since the fit is half wide with zoom false,
'set the fixed zoom to double the value from the dialog
'Might have to fudge this if the right border gets cut off.
.Zoom = 2 * fZoom

'Voila! the worksheet will fit the next time print preview is run.
End With



"Student" wrote:

> I have six worksheets generated by VBA using data copied from eight
> worksheets entered by hand, all in the same workbook. All specify 11 x 17
> landscape (except one VBA generated sheet that is landscape), Fit to 1 page
> wide with the radio button active, and Fit to 99 pages tall (I've tried false
> to get blank for this field), etc. The page setup dialogs have the same data
> in all fields.
>
> I've printed (and used preview) to both LJ 5Si and LJ 8000 DN, both simplex
> and duplex. I've tried reinstalling the printer. Both are network printers.
>
> The manually entered sheets print the full width of the printable area,
> about 16". The VBA generated sheets print and preview half the full width of
> the printable area, about 8". The behavior is the same on letter size.
> Manually changing page setup for the VBA generated sheets does not correct
> the problem although using zoom does allow statically printing full width.
> However, as the data changes, column widths will change and the zoom factors
> will have to be manually changed; this is not acceptable.
>
> I've tried computing the zoom factor from the width of the range, but the
> result is too large and I have to fudge the zoom downward by 4-13% depending
> upon the sheet. Obviously, this won't work unless I'm willing to reset the
> fudge factors; I'm not.
>
> Clearly, I'm missing something here. Any clue would be helpful.