Gary Brown said:John Mcgimpsey helped me with this about 8 years ago. Using an Excel 4
macro
dramatically increases the speed.
Below is my entire module. Lot of info. Print it out and read through
it.
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown
------------------------------------------------
Option Explicit
'/=======================================/
'Note: Excel 4 macro Help file is in the 1033 sub-folder of
' your Office folder [xlMacro.chm]
Const C As String = ","
Public Sub PageSetupXL4(Optional LeftHead As String, _
Optional CenterHead As String, _
Optional RightHead As String, _
Optional LeftFoot As String, _
Optional CenterFoot As String, _
Optional RightFoot As String, _
Optional LeftMarginInches As String, _
Optional RightMarginInches As String, _
Optional TopMarginInches As String, _
Optional BottomMarginInches As String, _
Optional HeaderMarginInches As String, _
Optional FooterMarginInches As String, _
Optional PrintHeadings As String, _
Optional PrintGridlines As String, _
Optional PrintComments As String, _
Optional PrintQuality As String, _
Optional CenterHorizontally As String, _
Optional CenterVertically As String, _
Optional Orientation As String, _
Optional Draft As String, _
Optional PaperSize As String, _
Optional FirstPageNumber As String, _
Optional Order As String, _
Optional BlackAndWhite As String, _
Optional Zoom As String)
On Error Resume Next
' Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead <> "" Then head = "&L" & LeftHead
If CenterHead <> "" Then head = head & "&C" & _
CenterHead
If RightHead <> "" Then head = head & "&R" & _
RightHead
If Not head = "" Then head = """" & head & """"
If LeftFoot <> "" Then foot = "&L" & LeftFoot
If CenterFoot <> "" Then foot = foot & "&C" & _
CenterFoot
If RightFoot <> "" Then foot = foot & "&R" & _
RightFoot
If Not foot = "" Then foot = """" & foot & """"
pgSetup = "PAGE.SETUP(" & head & C & foot & C & _
LeftMarginInches & C & RightMarginInches & C & _
TopMarginInches & C & BottomMarginInches & C & _
PrintHeadings & C & PrintGridlines & C & _
CenterHorizontally & C & CenterVertically & C & _
Orientation & C & PaperSize & C & Zoom & C & _
FirstPageNumber & C & Order & C & BlackAndWhite & C _
& PrintQuality & C & HeaderMarginInches & C & _
FooterMarginInches & C & PrintComments & C & Draft & _
")"
Application.ExecuteExcel4Macro pgSetup
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'NOTES ON MACRO:
'John Mcgimpsey: based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5@mara9"
'
'Gary brown: based on John McGimpsey's website [11/06/2005]
' http://www.mcgimpsey.com/excel/udfs/pagesetup.html
'Accessing a worksheet's PageSetup object is painfully slow in VBA.
'Seemingly unable to maintain a connection with the object,
'instead, VBA appears to go out to the printer driver for
'each operation, making a macro with 2 properties take twice as long
'as if only the LeftHeader property were set.
'Fortunately, you can still use XL4 macros, which can set all
'the parameters at once, from VBA. This macro, regardless of how
'many arguments you use, will take about the same amount of time
'as setting one parameter via VBA.
'/=======================================/
'Excel 4 Help for -
'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.
'
'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 character 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
'/=======================================/
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.