Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page Setup from VBA prints half wide
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
only prints half a page in excel | Excel Discussion (Misc queries) | |||
Prints Half Page | Excel Discussion (Misc queries) | |||
Excel 2007, only half of my page prints | Excel Discussion (Misc queries) | |||
office prints only a half of 1st page | Excel Discussion (Misc queries) | |||
why do my spreadsheet gridlines only prints half way on the page?. | Excel Worksheet Functions |