Custom Printing (2000)

Topic

New Reply

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

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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:

[indent]

---

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 10×14
17 11×17
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

---

[/indent]

Reply | Quote

I found the Excel 4 Macro Help file at http://support.microsoft.com/default.aspx?…kb;en-us;128185%5B/url%5D on the Microsoft support site. I was still having problems with the syntax. It had been too many years since I wrote an Excel 4 macro.

Reply | Quote

Hi Macropod

I’ve been trying out lots of these possibilities and now it’s time for this one . The major drawback with running code on existing sheets has been the slow speed of carrying out formatting on lots of rows. To this end, your suggestion of referencing existing cells in new worksheets sounds good. I have two queries about doing this:

o Would this slow up the initial opening of the workbook? (I know that will be hard to answer )

o If the original sheets change (modified data, new rows added etc.) is it easy to have these changes automatically reflected in the new hidden sheets. Obviously the first will be reflected in the referenced values changing, but what about the second?

thanks

Alan

Reply | Quote

(Edited by macropod on 16-Sep-02 09:18. see note indicated by ***)

Hi Alan,

In answer to your questions,

Yes, this will slow down the opening of the workbook – because it’ll require more disk space and memory. Depending on its size and complexity, though, the change in loading and calculation speed may or may not noticeable. Having said that, the sheer number of sheets you’re working with means that the effect will probably be noticeable.

For referencing purposes, you can use direct referencing in the form of:
=Sheet1!A1
While that’s fine for any rows & columns that you know won’t be deleted (eg table headings), it won’t pick up new ones (which can give serious but subtle errors in the printed results) and you’ll get #REF errors if referenced cells are deleted. The alternative is to use indirect referencing in the form of:
=offset(Sheet1!$A$1,row()-1,column()-1)
With this form, the data on your linked sheets won’t be affected unles the user deletes columnA and/or row1 on the source worksheet***. The disadvantages of this form are that it is a volatile formula, requires more memory, is probably slower to recalculate and, if you’ve formatted particular rows/columns on your ‘print’ worksheets based on an assumption about where things like totals etc will be ,and the user changes that by adding/deleting rows/columns, your formatting will be out. The last of these is probably the biggest issue, but can be managed with conditional formatting and by other means.

Cheers

*** or they do a cut/paste that moves another cell into A1 or relocates A1 to a new location on this or another worksheet.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

So use:

=OFFSET(INDIRECT(“Sheet1!A1”),row()-1,Column()-1,1,1)

Reply | Quote

Hi Jan,

Yes, of course! I hardly ever need to use INDIRECT(), since I don’t have to deal with the possibility that someone will trash the ‘key’ cell. Very useful for a variety of other things too.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

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

I’m not sure what you mean by ” Fully expand multilined rows.”

Reply | Quote

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 but I don’t understand the significance of last bit though:

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

cheers

Alan

Reply | Quote

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

Reply | Quote

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 >:-(

Alan

Reply | Quote

True, but saving and reopening the workbook that contains the code is not trivial.

Reply | Quote

Wouldnt something along the lines of

ActiveWorkbook.Save

‘ do things here

Application.DisplayAlerts = False
Workbooks.Open Filename:=”C:DataMyFile.xls”
Application.DisplayAlerts = True

Wouldn’t something like this reset it to where it was before the save? Only problem that I see would be if they wanted to make changes before they printed and then to close without saving those changes

Peter

Reply | Quote

Apart from your own remarks, that would work.

But if the code is in the same workbook as the one one wants to save, print and reopen, then there is a problem (which is what I was referring to).

Reply | Quote

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

    Worksheets("Sheet1").Cells.EntireRow.AutoFit

The significance of the lines of code:

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

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:

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

Reply | Quote

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

Reply | Quote

Hi Legare,

I’ve had a look at your code snippets on this thread and have a couple of observations/queries:
. Wouldn’t you need to store both the column widths and row heights, not just whether a column (or row: J = 1 to 65536) is visible?
. I noticed that you Dimmed margins as integers – I don’t think that would work unless the margins were in fact whole numbers.
. There’s a lot more page properties to capture/restore than you included. Surely all of them are needed to ensure full control?

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

1- No, you do not need to save the column widths if you are not going to change them. You can set Hidden to True, and when you set it back to False it will go back to the last width that was set for the column.

2- You are correct, the variables for saving margins should have been Dim as Double not Integer.

3- No, you only need to save what you are changing. Since accessing and setting PageSetup Properties can ve VERY slow, you should only save and set those that are absolutely necessary.

Reply | Quote

Hi legare,

[indent]

---

Since accessing and setting PageSetup Properties can ve VERY slow

---

[/indent]

or use the code in This Post

Reply | Quote

Hi again Legare,

Re 1 & 3, the problem as I see it is that you don’t know what column widths, row heights and page formatting a user might have applied that you don’t want to replicate in the custom report. Therefore, unless you replace them with the ones you require, the report you get may not have the correct formatting. Ineed, one of the previous posts identified a need to expand row heights to accommodate text wrapping (which could result from insufficient column width). Following on from that, I also thought the idea was to return the spreadsheet to its previous state after the report was printed.

Maybe I’ve got the wrong end of the stick, but in the end I think it would probably be far easier to add on one or more worksheets containing the required report formatting (as I originally suggested) instead of having to worry about saving & restoring the layout of other sheets or whether or not the workbook should be saved. Through code, the report sheet(s) could be kept hidden from the (typical) user and only accessed via the custom print button.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

All of this can very easily be done by using custom views.
They save all row heights, column widths, hidden rows/columns, page settings and I don’t exactly know what else.

Create the view you like, save it and you can call it back any time.
Also, if one saves the users current view (to a temporary name) and then opens a predefined view it is very simple to give the user his/her own view back…

Reply | Quote

I gave the custom views option a whirl, and abandoned the idea for a couple of reasons:

o The row height does NOT appear to be preserved. The multiline rows were set to autofit and showed correctly when I created the view, but were not restoered as such when I’d switch back to it.

o The views were very slow to come up (as discussed earlier in this thread) and I found it (marginally) quicker to use VBA code associated with a set of option buttons on a userform. This allowed the inclusion of autofit row height, that actually works , as well as the ability to restore any cell selection the user had made prior to switching view options.

Alan

Reply | Quote

Ah. OK.
Learnt something new again today.

Reply | Quote

Have you checked out the View, custom views method? I believe all you need to do now is create two views for the workbook, one for on-screen work, the other for printing purposes. Your code then sets the appropriate view before printing and sets the view back to screen mode after that.

How to do this:

– Set all your sheets as they should look when the user works with them.
– Choose View, custom view, click add, name the view and check all boxes.
– Repeat the same for the “print” view.

Now in your code use something like this:

activeWorkbook.CustomViews(“print”).Show
activeworkbook.selectedsheets.printout
activeWorkbook.CustomViews(“screen”).Show

Other advantage: if the user changes anything like (un)hiding rows/columns, choosing one of your views removes those changes.

Reply | Quote

Thanks Jan. I’d forgotten about Views (for Excel anyway, not Access ) but this also looks like a good option. I had essentially coded a “Print View” on a commandbar button already. The only difficulty here is that I don’t know exactly *what* the user view will be for a particular user. The way round it might be, from a single command button, to “capture” their settings as a custom view prior to printing, switch to Printing view, print, switch back to their “captured” view.

I’ll experiment

cheers

Alan

Reply | Quote

[indent]

---

The only difficulty here is that I don’t know exactly *what* the user view will be for a particular user. The way round it might be, from a single command button, to “capture” their settings as a custom view prior to printing, switch to Printing view, print, switch back to their “captured” view.

---

[/indent]

That sounds like a good workaround to me. You could even delete the view after restoring it I guess (didn’t look if one can do that)

Reply | Quote

Or perhaps not bother with deleting it, just overwriting it as needed. I guess it depends on what we’re allowed to do with our own spreadsheets

Alan

Reply | Quote

Just turn off screen updating so they will never know

Peter

Reply | Quote