Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Aug 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Watermarks (Excel 2K)

    We have several large spreadsheets that we want to print out with a watermark saying "confidential" printed in large outline type across each page. Excel does not seem to have the same capability as Word to insert word art into the header and get the text to print on each page. Is there any way to get Excel to do this easily so that the text is printed in the same format and position for each page? What HELP seems to recommend is to insert an object, but that will not apply to every page in the spreadsheet.

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Watermarks (Excel 2K)

    The best I could suggest would be inserting a graphic as the background of the sheet.
    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Watermarks (Excel 2K)

    redfearn

    Surely you can use the Header/Footer to print a Confidential, but this depends on the printer you are using and its driver software more than MS-Excel. I am working with an HP Color LaserJet 4550DN. And here are the steps I go through to make a watermark.

    1) Print the worksheet using the File|Print command. You will get the print dialog.
    2) From the Print Dialog select the printer you want to use, and hit Properties.
    3) See if you have any Watermark issues usually under Document Options | Printer Features.

    For my HP Color LaserJet 4550DN I have Watermark, Watermark font, Watermark size, Watermark angle, Watermark style, and Watermark where to print it, all pages or first page or what have you.

    Check with the maker of your printer if they have any updated drivers.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Watermarks (Excel 2K)

    WebGenii

    Will the background print in MS-Excel? I was under the impression that it is only for display purposes?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Lounger
    Join Date
    Aug 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Watermarks (Excel 2K)

    Unfortunately, the MS drivers that are installed don't have any of these options. I tried installing the PostScript driver for the printer (an HP8000), and there are no options. I may see if I can get updated drivers from HP. Thanks for the suggestion, though.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Watermarks (Excel 2K)

    If your printer and paper quality are up to it, you could use a 2 pass printing process
    Attached Files Attached Files

  7. #7
    Lounger
    Join Date
    Aug 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Watermarks (Excel 2K)

    Thanks for the suggestion and detailed instructions. We have so many pages to print, that we have just decided to put a footer on each page with the "confidential" in large font. That will be satisfactory.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Watermarks (Excel 2K)

    Redfern,

    The answer is yes there is a way to do what you want but it isn't easy and I won't be able to give you the entire answer because it is beyond my means. I actually posted an answer to you yesterday. But it got lost because of a lounge "eccentricity" that I didn't notice. So I'm posting again. However, if we can get some of the real experts to help out, the human race will be so much better off (at least that tiny fraction that use Excel AND wanted the answer to this question). <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Firstly, as Wassim pointed out:
    - you can always put "Confidential" or whatever into your header/footer for printing at the top/bottom of a page. But I do understand this is not what you want. You're looking for the Word capability where you go into the mode where you're viewing the Header/Footer but you put a graphic somewhere in the middle of the page (remembering, of course, to change the wrap options).
    - the Worksheet background doesn't work - that's only for viewing. I would have thought that would be for printing but, alas, it doesn't seem to be (at least not on my Excel 97).

    So what the solution I'm about to propose can do is put the graphic in the middle of every page. It can NOT put the graphic at the top/bottom where you put things like date, page number, etc. What the solution comes down to is writing VBA code that goes thru the collection of sheets, divides each sheet into print areas, pastes a graphic into the print area, and then prints each area. The magic is in the VBA that finds the print areas. See below for details if you're still game (or some of our other experts are).

    I had a post a few weeks ago where I talked about Manual Page numbering. To understand this solution, you should start there. Here's the <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=111195&page=& view=&sb=&o=&vc=1#Post111195> URL </A> for it.

    Hans Pottel's first reply on the above thread pointed to an <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=18276&Search= true&Forum=xl&Words=number> Excel4 macro </A> solution. This is probably NOT useful in this case but worth noting anyway.

    Hans Pottel's second reply on the above thread was the gem. This pointed to <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=81867&Search= true&Forum=xl&Words=page> another thread </A> where he showed some code that showed how many page breaks Excel will divide a sheet into (both vertically and horizontally) and a way of finding where each of those breaks is.

    So now imagine a looping process (forget the loop for each sheet - assume that as a given as indicated in my thread on Manual Page numbering) that finds the page breaks - hence dividing the sheet into rectangular areas that each would fit on 1 physical piece of paper (just like what Excel would do when printing a sheet that required more than 1 piece of paper for all the cells of the sheet). What Hans' code did was dynamically set the Print Area inside of this loop to that rectangular area. So he used the statement:

    ActiveSheet.PageSetup.PrintArea = PrArea

    The ...PrintArea is an Excel attribute of PageSetup(?). PrArea is a string variable that Hans' created dynamically based on the locations of the page breaks. For example, the first time thru the loop it might have the value something like "A1:H45". This is the same you can do w/o VBA by selecting a bunch of cells (say A1:H45), going to the File Menu, down to Print Area menu item (about half way down in 97), and choosing Set Print Area. The 2nd time thru the loop, PrArea might be "A46:H90"; 3rd time might be "I1:P45", etc (printing the parts of the sheet down then over).

    Once you set the Print Area, you need to print it. Hans' statement was
    ActiveSheet.PrintOut copies:=1

    This does the actual printing. Now you're at the end of the loop, iterate for the next print area and print again.

    I admit I am intrigued by the challenge of using the above to control printing. It would allow me to solve the problem I posted in my Manual Numbering thread. Alas, no time.

    However, there's one more piece needed for your problem. How to get the graphic into each print area. This is where my value-add (and it ain't that much value) comes in. There are 2 ways:
    - use the Insert menu, Picture to read in a graphic file (maybe previously save from WordArt)
    - create the item in this spreadsheet

    In either case, the item would be in some out of the way place that you're not printing (maybe another sheet).

    So the idea is to position that graphic while you're in the loop defining print areas at a cell in that print area. For example, I put in a few cell entries - enough to result in 2 horizontal by 2 vertical pages (I knew this because I went into Print Preview, saw I had 4 pages, and back in Excel saw the horizontal and vertical page break dotted lines). Then I created a WordArt object (2nd way above) and copied it to the clipboard. Then I turned on the macro recorder, clicked in each of the print areas, and pasted my WordArt object. This gave the following code:

    <pre>Sub Macro2()
    '
    ActiveSheet.Shapes("WordArt 1").Select
    Selection.Copy
    'actually the recorder was on when I selected the WordArt object and copied it
    'that's the 2 steps above
    Range("B3").Select
    ActiveSheet.Paste
    Range("K3").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=30 'this could be ignored
    Range("A52").Select
    ActiveSheet.Paste
    End Sub
    </pre>


    So by selecting a cell somewhere in the print area (which you know already) and pasting the WordArt object, you'd get exactly what you want (of course, make sure the WordArt object is formatted with NoFill for it - you don't want to block the cell entries). Need to make sure the selected cell is such that the WordArt object doesn't go past the page breaks for that print area. Not totally sure how to do that but here are some leads.

    To finely position the object within the print area, a little more work is needed. Eyeballing the object to see its height and width is a start. But you also have to know the dimensions of the print area. Remember that you can click your mouse on the lines dividing the col/row headers (eg, click on the line between "A" and "B" to see the width of col A). But one print area might have 8 cols while the next one to the right only has 7 cols because one of its cols is wider than others. But VBA comes to the rescue again.

    Determining the height of the object is not hard. In VBA you can do:
    ActiveSheet.Shapes("WordArt 1").Height

    which gave me a number like 51. I think the units are points. This seems right since my WordArt object took up almost exactly 4 rows with each set at the initial value of 12.75. Similarly for width.

    While there is nothing similar like
    ActiveSheet.PageSetup.PrintArea.Height

    all is not lost. Since you know the range of cells covered by PrintArea (which was Hans' variable PrArea), you can do:
    range(PrArea).Height

    to get its height. Again similarly for width.

    Figuring out where, numerically, your WordArt object for centering should be pasted is now trivial. The problem is, as far as I know, is that you have to select a cell, as I did in my code above, before doing the paste. So there would have to be some way of determining what cell corresponded (as closely as possible) to the numerically-derived location. That's beyond me.

    Of course, you can always select the upper left cell of the print area and hope your WordArt object covers most of the print area. This actually wouldn't be all that bad since Excel will define print areas that are roughly the same size in terms of height and width (taking into account margins, etc). Then ignore the above few paragraphs.

    So the above is a way of doing what you want. At least the theory sounds good. Problem is reducing this to practice - above my means.

    Good luck!

    Fred

  9. #9
    Lounger
    Join Date
    Aug 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Watermarks (Excel 2K)

    Thanks for the detailed information. As I indicated in an earlier post, we have just decided to use a footer to label each page confidential. I think the best way to handle this would be a printer driver that includes the watermark capability in the driver. This does not exist in the HP8000 drivers that comes with Win2K, but I also tried downloading the HP PS driver from the HP web, and that also does not appear to have a watermark option. I can remember a long time ago actually imbedding PS commands in the header text and getting the printer to apply watermarks, but that was a long time ago. Agaiin, thanks for the suggestions.

  10. #10
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Watermarks (Excel 2K)

    Not ideal, but:

    Open Word
    File > New
    Insert > Bookmark (your ready made Confidential graphic)
    Insert > File (your xls file)
    Print

  11. #11
    Lounger
    Join Date
    Aug 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Watermarks (Excel 2K)

    This is a response to all who have been kind enough to help with this issue. I have found the solution - if you have a current HP LaserJet printer, you can download and install the PCL6 driver. This driver supports watermarks in the printer. No need to fool around with either Word or Excel - it works fine for either. I tested this on an HP LJ8000 and it works just fine.

Posting Permissions

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