Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Printing (2000)

    I'm wanting to create a button on a custom toolbar to set up the printing parameters for a worksheet, then print it. The essential feature will be to restore the user's settings after the print job. The custom settings will have to set:

    o Paper size/ orientation
    o Margins
    o Header/ footer
    o Hide/ show certain columns (in the printed output at least)
    o Fully expand multilined rows, as above

    I'm at a loss as to how to "capture" the original user settings for the above, then restore them after the printing completes. Any ideas appreciated :-)

    thanks

    Alan

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Custom Printing (2000)

    Hi Alan,

    Rather than go to all the trouble of changing the worksheet's layout etc and restoring it afterwards, why not just add another worksheet to the existing workbook that:
    . is pre-formmated the way you want it;
    . has formulae linking to the required cells/ranges in the rest of the workbook; and
    . has its cells locked and protection turned on to prevent unautorised changes?
    Your 'print' button then only needs to fire a macro to print your new worksheet. That sounds like a lot less effort to me.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    I had thought of going this route, but was put off by the fact that there are 11 worksheets in the workbook, all up for the same treatment. This would mean *another* 11 just for printing purposes. I had thought of a modification to your suggestion though. Namely, cloning the target worksheet, applying the customisation to the clone, printing, then destroying the clone. Does this sound more viable?

    Also, do you know where to look for the methods appropriate to printer setup parameters? I'm having trouble finding a list of everything available that might need to be attended to.

    thanks

    Alan

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    In Excel 2000 there is a memory leak using the pagesetup object. Using this method, you could set the parameters for the pagesetup object for each sheet to variables prior to printing, then set them back after printing.

    Using the ExecuteExcel4Macro method will avoid the memory leak, but would be more difficult than using the pagesetup object.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Custom Printing (2000)

    Hi Alan,

    Yes, I think having a 'print' version of your workbook might be best, though I wouldn't necesssarily recommend trashing it afterwards. Linking it to the source workbook might work better in the long run. You could use formulae or a macro for this.

    The list of print/page setup paprameters is quite extensive, and you might have to save & restore quite all of them (for each & every worksheet). A quick run-through with the macro recorder (XL97) gave me:

    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With

    ActiveSheet.PageSetup.PrintArea = ""

    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(1.00)
    .RightMargin = Application.InchesToPoints(1.00)
    .TopMargin = Application.InchesToPoints(1.00)
    .BottomMargin = Application.InchesToPoints(1.00)
    .HeaderMargin = Application.InchesToPoints(0.25)
    .FooterMargin = Application.InchesToPoints(0.25)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperA4
    .FirstPageNumber = xlAutomatic
    .Order = xlOverThenDown
    .BlackAndWhite = False
    .Zoom = 100
    End With

    If you were going to modify the user's workbook, you'd presumably have to save & restore all of these, since you won't necessarily know what they've used and you'll want only your formatting etc to be reproduced in your version.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    Would it be possible to close the workbook without saving after the print and then reopen it?

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Custom Printing (2000)

    Wot? After all that careful re-formatting to get the print just so?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    I would go for the cloning thing. Relatively fast, simple to remove after the print.

    You could consider using the ExecuteExcel4Macro method to speed things up. Here is some sample code I found:

    For Each wks In ActiveWindow.SelectedSheets
    wks.Activate
    Orient = Application.ExecuteExcel4Macro("Get.Document(53)")
    If Orient = 1 Then
    pleft = 0.75
    Top = 0.5
    ElseIf Orient = 2 Then
    pleft = 0.5
    Top = 0.75
    End If
    pright = 0.5
    bot = 0.5
    head_margin = 0.5
    foot_margin = 0.25

    PsetUp = "PAGE.SETUP(" & head & "," & foot & "," & pleft & "," & pright
    & ","
    PsetUp = PsetUp & Top & "," & bot & "," & hdng & "," & grid & "," &
    h_cntr & ","
    PsetUp = PsetUp & v_cntr & "," & Orient & "," & paper_size & "," &
    pscale & ","
    PsetUp = PsetUp & pg_num & "," & pg_order & "," & bw_cells & "," &
    quality & ","
    PsetUp = PsetUp & head_margin & "," & foot_margin & "," & notes & "," &
    Draft & ")"
    Application.ExecuteExcel4Macro PsetUp
    Next

    The Help file of these XL4 macro's says:

    <hr>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
    <hr>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    Unless I misunderstand your problem, capturning the properties should be fairly simple:

    <pre>Dim iPaperSize As Integer, iOrientation As Integer
    Dim iTopMargin As Integer, iBottomMargin As Integer
    Dim iLeftMargin As Integer, iRightMargin As Integer
    Dim strLeftHeader As String, strCenterHeader As String
    Dim strRightHeader As String, strLeftFooter As String
    Dim strCenterFooter As String, strRightFooter As String
    Dim bColC As Boolean
    With Worksheets("Sheet1").PageSetup
    iPaperSize = .PaperSize
    iOrientation = .Orientation
    iLeftMargin = .LeftMargin
    iRightMargin = .RightMargin
    iTopMargin = .TopMargin
    iBottomMargin = .BottomMargin
    strLeftHeader = .LeftHeader
    strCenterHeader = .CenterHeader
    strRightHeader = .RightHeader
    strLeftFooter = .LeftFooter
    strCenterFooter = .CenterFooter
    strCenterFooter = .CenterFooter
    End With
    With Worksheets("Sheet1")
    bColC = Range("C:C").EntireColumn.Hidden
    End With
    </pre>


    I'm not sure what you mean by " Fully expand multilined rows."
    Legare Coleman

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    It actually doesn't look as daunting as I'd thought :-) This now makes a capture, set and restore look a lot more attractive, but I'd still need to restore the state of any columns that might be hidden in the original user view.

    The worksheet is usually viewed with each row showing just the first line (RowHeight = 12.75) but for printing purposes, all rows must show all of the lines of text in each cell. To this end, I'd need to use:

    Cells.Select
    Selection.rows.AutoFit

    Thanks for the code Legare. I will have a play [img]/forums/images/smilies/smile.gif[/img] but I don't understand the significance of last bit though:

    With Worksheets("Sheet1")
    bColC = Range("C:C").EntireColumn.Hidden
    End With

    cheers

    Alan

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    I still can't quite see why you want to capture and restore. Presumably this will retun it to the same state it was in before you made your alterations. Surley saving it before you change things and then reopening it with out saving your changes will do the same thing?

    Peter

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    The following line of code will AutoFit all of the rows on worksheet Sheet1:

    <pre> Worksheets("Sheet1").Cells.EntireRow.AutoFit
    </pre>


    The significance of the lines of code:

    <pre> With Worksheets("Sheet1")
    bColC = Range("C:C").EntireColumn.Hidden
    End With
    </pre>


    Is that your message said that you needed to save whether or not certain columns in the worksheet are hidden. That code saves the hidden property of column C in the boolean variable bColC. You could do the same for all of the columns you need to save. You could save the status of all columns by using a loop and putting the result into an array, like this:
    <pre>Dim bColHid(1 To 256) As Boolean
    Dim I As Long
    For I = 1 To 256
    bColHid(I) = Columns(I).EntireColumn.Hidden
    Next I
    End Sub
    </pre>

    Legare Coleman

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    I totally agree with you Peter - I've also suggested this and other "logical" ways of achieving the result, but the company I'm writing this for want their users to be able to "print at will" and return things to the way they were before printing. Mine is not to reason why I'm afraid <sigh> >:-(

    Alan

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    Thanks Legare. I now see the significance of the code you provided (now that I'm posting/reading in the daylight ;-)).

    I'll also adapt your autofit code for the Current Worksheet, so that it works from a toolbar button for any sheet.

    cheers

    Alan

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Printing (2000)

    True, but saving and reopening the workbook that contains the code is not trivial.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •