Simulate header / footer and insert watermark images using xlam macros

So, after fighting a bit with this issue, I'm looking for a workaround of the excel macro limitations.

What I need is to place a couple of logo images at the top and bottom of the first sheet, and a watermark image across all sheets (which requires transparency on the image).

This works when placing it in the header / footer, and using it locally, but when using a macro to set the images, either the images has to have a static location, or the images has to be created / generated on the fly, and it seems like there's a lot of limitations on that process.

So I was thinking, what about placing the images at specific locations of each sheet to simulate the effect of placing them in the header and footer? I really hope it's easier to place an actual image in a sheet at some specific location, than it is to place it in the header & footer.

The challenge here would be the placement. The current setting has a bunch of sheets all scaled to fit 1 page exactly, but a few will have 2 or more pages. I'm really not sure how to simulate the effect of a header / footer and have an image automatically be added as a watermark on every page.

So .. is this possible?

I have successfully used the following code to create a WaterMark on sheets. I hope this helps, and does not duplicate your previous efforts.

Lewis

Please Login or Register  to view this content.

Hi LJMetzger

Thank you for taking the time to answer my issue!

However, I can't seem to follow your code.

The 2 subroutines simply insert wordart text across 1 page, and the positioning in relation to pagesize is off.

Furthermore the text doesn't seem to be transparent, even though I can see a transparency set in the code... not sure if that's a bug.

I'm afraid I need something a bit more complicated to take multiple pages into account and place the image or text properly.

Remember the challenge is not to create a watermark (that part is easy), it's about simulating the functionality of the header which has a problem with accepting a transparent image when using a macro (only a filename is valid).

Please let me know if you come up with anything else!

Hi TheRune,

If you can upload a sample file that contains 2 pages of the correct size, that contain watermarks that you manually insert, maybe I can help. That way I can try to duplicate what you have with code.

To attach a Workbook:
Click Advanced (next to quick post),
Scroll down until you see "Manage Attachments",
Click that then select "add files" (top right corner).
Click "Select Files" find your file, click "open" click "upload"
Once the upload is completed the file name will appear below the input boxes in this window.
Click "Done" at bottom right to close the Attachment Manager.
Click "Submit Reply"

Lewis

Hi LJMetzger

The macro should work with any pagesize, not just an example. Besides my target files are all confidential so I really can't share an example with you. Sorry.

The thing is, if you create a new excel file, add the macros and simply run them, they will work as I described and simply place text across most of the first page and part of the second page.

So if you want a test case, simply create a new excel file, change the print settings for sheet 1 2 and 3 to include a different number of cells on each sheet and make sure to enter "test" in a cell on at least 2 pages of each sheet.

If you want me to create that testfile and attach it, let me know.

I would prefer your test file, with sample WaterMarks as you would like them positioned.

Hi LJMetzger

I found a different workaround, keeping the images in the header & footer and saving image locations in the registry.

I thank you for all your time on this, but I will not be working more on a solution based on this workaround. Sorry.

Hi TheRune,

There is no need for an apology. The important thing is that you found a solution to your problem that you are comfortable with.

Lewis