PDA

View Full Version : Print Setup via VBA



vassili
07-22-2007, 08:41 PM
is there a faster way of to code how the page is setup to print than this?

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.393700787401575)
.BottomMargin = Application.InchesToPoints(0.196850393700787)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 1200
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.PrintErrors = xlPrintErrorsDisplayed
End With

i have my main code generates a bunch of sheets from user input and the page length of every sheet varies with input. therefore, if i don't setup a general sheet layout, the printer will 100% of the time cut off some of the sides, top and bottom.

however, i found that if i incorporated the above code into my main code is takes forever to generate 1 sheet. where as before it would take < 1 sec, not it will take 4-5 sec. the computer seems to choke on every single line in that code. is there a better way?

thanks in advance

johnske
07-22-2007, 10:11 PM
Yes, XLM4.0 macros are faster for this (probably the only 'real' use for XLM4.0 macros these days). Example use:

Option Explicit

Sub UseXLM4pageSetUp()
Application.ExecuteExcel4Macro "PAGE.SETUP(, , 1.5, 1.5, 1.5, 1.5" & _
", 0, False, False, False, 1, 1, True, 1, 1,False, , " & _
"1, 1, False)"
End Sub
The arguments are in the XLM4.0 macros functions Help file (you can download that from here (http://xlvba.3.forumer.com/index.php?act=ST&f=15&t=331)) and I'll leave you to sort them out, but note that a couple of the arguments you're using were probably not available in XLM4 format, so you'll need to use ordinary VBA for those arguments...

geekgirlau
07-22-2007, 11:13 PM
Also, make note that some of the settings are the default options, so you don't need to explicitly change them in your code. For example,


.Draft = False
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False


will be set to these values even if you comment out those lines in the code, so this may help you when sorting out the parameters that are not available in XLM4.

vassili
07-23-2007, 12:37 AM
Yes, XLM4.0 macros are faster for this (probably the only 'real' use for XLM4.0 macros these days). Example use:

Option Explicit

Sub UseXLM4pageSetUp()
Application.ExecuteExcel4Macro "PAGE.SETUP(, , 1.5, 1.5, 1.5, 1.5" & _
", 0, False, False, False, 1, 1, True, 1, 1,False, , " & _
"1, 1, False)"
End Sub
The arguments are in the XLM4.0 macros functions Help file (you can download that from here (http://xlvba.3.forumer.com/index.php?act=ST&f=15&t=331)) and I'll leave you to sort them out, but note that a couple of the arguments you're using were probably not available in XLM4 format, so you'll need to use ordinary VBA for those arguments...

i can't seem to install the exe. it says i need to have office 2000 installed. however, i have office xp. could you just quickly give me the run down on what each setting does?

johnske
07-23-2007, 12:44 AM
Copied directly from the Help file...



PAGE.SETUP

Macro Sheets Only
Equivalent to clicking the Page Setup command on the File menu. Use PAGE.SETUP to control the printed appearance of your sheets.
There are three syntax forms of PAGE.SETUP. Syntax 1 applies if a sheet or macro sheet is active; syntax 2 applies if a chart is active; syntax three applies to Visual Basic modules and the info Window.
Arguments correspond to check boxes and text boxes in the Page Setup dialog box. Arguments that correspond to check boxes are logical values. If an argument is TRUE, Microsoft Excel selects the check box; if FALSE, Microsoft Excel clears the check box. Arguments for margins are always in inches, regardless of your country setting.
Syntax 1
Worksheets and macro sheets
PAGE.SETUP(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)
PAGE.SETUP?(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)
Syntax 2
Charts
PAGE.SETUP(head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale, pg_num, bw_chart, quality, head_margin, foot_margin, draft)
PAGE.SETUP?(head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale, pg_num, bw_chart, quality, head_margin, foot_margin, draft)
Syntax 3
Visual Basic Modules and the Info Window
PAGE.SETUP(head, foot, left, right, top, bot, orient, paper_size, scale, quality, head_margin, foot_margin, pg_num)
PAGE.SETUP?(head, foot, left, right, top, bot, orient, paper_size, scale, quality, head_margin, foot_margin, pg_num)
Head specifies the text and formatting codes for the header for the current sheet . For information about formatting codes, see "Remarks" later in this topic.
Foot specifies the text and formatting codes for the workbook footer.
Left corresponds to the Left box and is a number specifying the left margin.
Right corresponds to the Right box and is a number specifying the right margin.
Top corresponds to the Top box and is a number specifying the top margin.
Bot corresponds to the Bottom box and is a number specifying the bottom margin.
Hdng corresponds to the Row & Column Headings check box. Hdng is available only in the sheet and macro sheet form of the function.
Grid corresponds to the Cell Gridlines check box. Grid is available only in the sheet and macro sheet form of the function.
H_cntr corresponds to the Center Horizontally check box in the Margins panel of the Page Setup dialog box.
V_cntr corresponds to the Center Vertically check box in the Margins panel of the Page Setup dialog box.
Orient determines the direction in which your workbook is printed.
OrientPrint format1Portrait2Landscape
Paper_size is a number from 1 to 26 that specifies the size of the paper.
Paper_sizePaper type1Letter2Letter (small)3Tabloid4Ledger5Legal6Statement7Executive8A39A410A4 (small)11A512B413B514Folio15Quarto1610x141711x1718Note19ENV920ENV1021ENV112 2ENV1223ENV1424C Sheet25D Sheet26E Sheet

Scale is a number representing the percentage to increase or decrease the size of the sheet. All scaling retains the aspect ratio of the original.

<LI class=DT2>To specify a percentage of reduction or enlargement, set scale to the percentage.

<LI class=DT2>For worksheets and macros, you can specify the number of pages that the printout should be scaled to fit. Set scale to a two-item horizontal array, with the first item equal to the width and the second item equal to the height. If no constraint is necessary in one direction, you can set the corresponding value to #N/A.
Scale can also be a logical value. To fit the print area on a single page, set scale to TRUE.Pg_num specifies the number of the first page. If zero, sets first page to zero. If "Auto" is used, then the page numbering is set to automatic. If omitted, PAGE.SETUP retains the existing pg_num.
Pg_order specifies whether pagination is left-to-right and then down, or top-to-bottom and then right.
Pg_orderPagination1Top-to-bottom, then right2Left-to-right, then down

Bw_cells is a logical value that specifies whether to print cells and all graphic objects, such as text boxes and buttons, in color.

<LI class=DT2>If bw_cells is TRUE, Microsoft Excel prints cell text and borders in black and cell backgrounds in white.
If bw_cells is FALSE , Microsoft Excel prints cell text, borders, and background patterns in color (or in gray scale).Bw_chart is a logical value that specifies whether to print chart in color.
Size is a number corresponding to the options in the Chart Size box, and determines how you want the chart printed on the page within the margins. Size is available only in the chart form of the function.
SizeSize to print the chart1Screen size2Fit to page3Full page
Quality specifies the print quality in dots-per-inch. To specify both horizontal and vertical print quality, use an array of two values.
Head_margin is the placement, in inches, of the running head margin from the edge of the page.
Foot_margin is the placement, in inches, of the running foot margin from the edge of the page.
Draft corresponds to the Draft Quality checkbox in the Sheet tab and in the Chart tab of the Page Setup dialog box. If FALSE or omitted, graphics are printed with the sheet. If TRUE, no graphics are printed.
Notes specifies whether to print cell notes with the sheet. If TRUE, both the sheet and the cell notes are printed. If FALSE or omitted, just the sheet is printed.
Remarks
Microsoft Excel no longer requires you to enter formatting codes to format headers and footers, but the codes are still supported and recorded by the macro recorder. You can include these codes as part of the head and foot text strings to align portions of the header or footer to the left, right, or center; to include the page number, date, time, or workbook name; and to print the header or footer in bold or italic.
Formatting codeResult&LLeft-aligns the characters that follow.&CCenters the characters that follow.&RRight-aligns the characters that follow.&BTurns bold printing on or off (now obsolete).&ITurns italic printing on or off.&UTurns single underlining printing on or off.&STurns strikethrough printing on or off.&OTurns outline printing on or off (Macintosh only).&HTurns shadow printing on or off (Macintosh only).&DPrints the current date.&TPrints the current time.&APrints the name of the sheet&FPrints the name of the workbook.&PPrints the page number.&P+numberPrints the page number plus number.&P-numberPrints the page number minus number.&&Prints a single ampersand.& "fontname, fontstyle"Prints the characters that follow in the specified font and style. Be sure to include a comma immediately following the fontname, and double quotation marks around fontname and fontstyle.&nnPrints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.&NPrints the total number of pages in the workbook.&EPrints a double underline&XPrints the character as superscript&YPrints the character as subscript
Related Functions
DISPLAY (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1033\XLMACRO.CH M::/html/xlfctdisplay.htm) Controls screen and Info Window display
GET.DOCUMENT (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1033\XLMACRO.CH M::/html/xlfctgetdocument.htm) Returns information about a workbook
PRINT (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1033\XLMACRO.CH M::/html/xlfctprint.htm) Prints the active workbook
WORKSPACE (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1033\XLMACRO.CH M::/html/xlfctworkspace.htm) Changes workspace settings
Sub HelpPopup(sFile,sID)L_SecurityT1_ErrorMessage="Help can't show you this procedure because the security setting in your browser is set too high"L_SecurityT2_ErrorMessage="or the ActiveX control Ouactrl.ocx didn't install correctly."L_SecurityE1_ErrorMessage="- Select a lower security setting in your browser"L_SecurityE2_ErrorMessage="- If you receive this message after selecting a lower security setting, please see your system"L_SecurityE3_ErrorMessage=" administrator for help troubleshooting the installation of the ActiveX control Ouactrl.ocx"L_SecurityE4_ErrorMessage=" located in the folder you installed Microsoft Office to."sSecurityMSG=L_SecurityT1_ErrorMessage & chr(13) & L_SecurityT2_ErrorMessage & chr(13) & chr(13) & L_SecurityE1_ErrorMessage & chr(13) & L_SecurityE2_ErrorMessage & chr(13) & L_SecurityE3_ErrorMessage & chr(13) & L_SecurityE4_ErrorMessageL_App_DialogTitle="Microsoft Office Help"On Error Resume Nextr=oua.HelpPopup(sFile,sID)If Err0 Then Msgbox sSecurityMSG,48,L_App_DialogTitleEnd Sub

Bob Phillips
07-23-2007, 01:09 AM
I get a bit more in mine?

Macro Sheets Only
Equivalent to choosing the Page Setup command from the File menu. Use PAGE.SETUP to control the printed appearance of your sheets.
There are three syntax forms of PAGE.SETUP. Syntax 1 applies if a sheet or macro sheet is active; syntax 2 applies if a chart is active; syntax three applies to Visual Basic modules and the info Window.
Arguments correspond to check boxes and text boxes in the Page Setup dialog box. Arguments that correspond to check boxes are logical values. If an argument is TRUE, Microsoft Excel selects the check box; if FALSE, Microsoft Excel clears the check box. Arguments for margins are always in inches, regardless of your country setting.

Syntax 1

Worksheets and macro sheets
PAGE.SETUP(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)
PAGE.SETUP?(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)

Syntax 2

Charts
PAGE.SETUP(head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale, pg_num, bw_chart, quality, head_margin, foot_margin, draft)
PAGE.SETUP?(head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale, pg_num, bw_chart, quality, head_margin, foot_margin, draft)

Syntax 3

Visual Basic Modules and the Info Window
PAGE.SETUP(head, foot, left, right, top, bot, orient, paper_size, scale, quality, head_margin, foot_margin, pg_num)
PAGE.SETUP?(head, foot, left, right, top, bot, orient, paper_size, scale, quality, head_margin, foot_margin, pg_num)
Head specifies the text and formatting codes for the header for the current sheet . For information about formatting codes, see "Remarks" later in this topic.
Foot specifies the text and formatting codes for the workbook footer.
Left corresponds to the Left box and is a number specifying the left margin.
Right corresponds to the Right box and is a number specifying the right margin.
Top corresponds to the Top box and is a number specifying the top margin.

Bot corresponds to the Bottom box and is a number specifying the bottom margin.
Hdng corresponds to the Row & Column Headings check box. Hdng is available only in the sheet and macro sheet form of the function.
Grid corresponds to the Cell Gridlines check box. Grid is available only in the sheet and macro sheet form of the function.
H_cntr corresponds to the Center Horizontally check box in the Margins panel of the Page Setup dialog box.
V_cntr corresponds to the Center Vertically check box in the Margins panel of the Page Setup dialog box.
Orient determines the direction in which your workbook is printed.

Orient Print format

1 Portrait
2 Landscape

Paper_size is a number from 1 to 26 that specifies the size of the paper.

Paper_size Paper type

1 Letter
2 Letter (small)
3 Tabloid
4 Ledger
5 Legal
6 Statement
7 Executive
8 A3
9 A4
10 A4 (small)
11 A5
12 B4
13 B5
14 Folio
15 Quarto
16 10x14
17 11x17
18 Note
19 ENV9
20 ENV10
21 ENV11
22 ENV12
23 ENV14
24 C Sheet
25 D Sheet
26 E Sheet

Scale is a number representing the percentage to increase or decrease the size of the sheet. All scaling retains the aspect ratio of the original.

To specify a percentage of reduction or enlargement, set scale to the percentage.
For worksheets and macros, you can specify the number of pages that the printout should be scaled to fit. Set scale to a two-item horizontal array, with the first item equal to the width and the second item equal to the height. If no constraint is necessary in one direction, you can set the corresponding value to #N/A.
Scale can also be a logical value. To fit the print area on a single page, set scale to TRUE.

Pg_num specifies the number of the first page. If zero, sets first page to zero. If "Auto" is used, then the page numbering is set to automatic. If omitted, PAGE.SETUP retains the existing pg_num.
Pg_order specifies whether pagination is left-to-right and then down, or top-to-bottom and then right.

Pg_order Pagination

1 Top-to-bottom, then right
2 Left-to-right, then down

Bw_cells is a logical value that specifies whether to print cells and all graphic objects, such as text boxes and buttons, in color.

If bw_cells is TRUE, Microsoft Excel prints cell text and borders in black and cell backgrounds in white.
If bw_cells is FALSE , Microsoft Excel prints cell text, borders, and background patterns in color (or in gray scale).

Bw_chart is a logical value that specifies whether to print chart in color.
Size is a number corresponding to the options in the Chart Size box, and determines how you want the chart printed on the page within the margins. Size is available only in the chart form of the function.

Size Size to print the chart

1 Screen size
2 Fit to page
3 Full page

Quality specifies the print quality in dots-per-inch. To specify both horizontal and vertical print quality, use an array of two values.
Head_margin is the placement, in inches, of the running head margin from the edge of the page.
Foot_margin is the placement, in inches, of the running foot margin from the edge of the page.
Draft corresponds to the Draft Quality checkbox in the Sheet tab and in the Chart tab of the Page Setup dialog box. If FALSE or omitted, graphics are printed with the sheet. If TRUE, no graphics are printed.
Notes specifies whether to print cell notes with the sheet. If TRUE, both the sheet and the cell notes are printed. If FALSE or omitted, just the sheet is printed.

Remarks

Microsoft Excel no longer requires you to enter formatting codes to format headers and footers, but the codes are still supported and recorded by the macro recorder. You can include these codes as part of the head and foot text strings to align portions of the header or footer to the left, right, or center; to include the page number, date, time, or workbook name; and to print the header or footer in bold or italic.

Formatting code Result

&L Left-aligns the characters that follow.
&C Centers the characters that follow.
&R Right-aligns the characters that follow.
&B Turns bold printing on or off (now obsolete).
&I Turns italic printing on or off.
&U Turns single underlining printing on or off.
&S Turns strikethrough printing on or off.
&O Turns outline printing on or off (Macintosh only).
&H Turns shadow printing on or off (Macintosh only).
&D Prints the current date.
&T Prints the current time.
&A Prints the name of the sheet
&F Prints the name of the workbook.
&P Prints the page number.
&P+number Prints the page number plus number.
&P-number Prints the page number minus number.
&& Prints a single ampersand.

& "fontname, fontstyle" Prints the characters that follow in the specified font and style. Be sure to include a comma immediately following the fontname, and double quotation marks around fontname and fontstyle.
&nn Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
&N Prints the total number of pages in the workbook.
&E Prints a double underline
&X Prints the character as superscript
&Y Prints the chararcter as subscript

Related Functions

DISPLAY Controls screen and Info Window display
GET.DOCUMENT Returns information about a workbook
PRINT Prints the active workbook
WORKSPACE Changes workspace settings

List of Command-Equivalent Functions

johnske
07-23-2007, 01:24 AM
Yes, it didn't copy-paste properly for some reason. Weird, I just did a select all > copy > paste. :dunno

If you still want the complete Help file see it this (http://xlvba.3.forumer.com/index.php?showtopic=331&st=0&) works (2nd post - I just zipped the Help file itself)...

vassili
07-23-2007, 08:23 PM
thanks for the help guys, gonna give this a try.