Creating a footer on an EXCEL document.

[bnstaylor]

I have a rather large EXCEL oducment which I am required to use as a DELIVERY NOTE.

The document is divided into a header, product line details and a footer.

The document can be one or several pages long.

I am required to print the footer only on the first page.

I can print the header okay. It prints on every page.

I can sort of print the details ok. I say, can sort of as the detail heading line only prints on the first page..... What can I say !!

The BottomMargin is set to 340 and the FooterMargin is set to 30.

I have GooGled, PEGed and ProgressTalked EXCEL to death but to no avail.

All I simply want to know is how how to print a footer on the first page of an EXCEL document or the name of a document that tells me how to do this (e.g. is there a Progress document that tells you all aout this?)

Any helpful advice is greatly appreciated.

Had no luck in the development forum, so am hoping might have better luck here.


Regards, Barry.​

 

[Cringer]

There's no need to cross post. I've closed your other one. If nobody responds then it's probable that nobody knows how to help.

 

[Rob Fitzpatrick]

My understanding of your requirement: you want to use ABL to produce a new Excel workbook (or perhaps open and then edit an existing one). This workbook contains a worksheet that, when printed, may be one or several pages long. You want the first page, and only the first page, to have a particular footer. You don't want a footer on the subsequent pages.

Being the simple type, my typical approach here is this:

1. Try to do what you want to do, manually, in Excel. No ABL in sight. Can you do it? If so, there's a good chance you can also do it programmatically.
2. Repeat the process, with the macro recorder running.
3. Open the VBA IDE and look at the code that was produced. It won't be the kind of code you might write, it will be overly verbose, and it obviously won't have flow control like loops and so forth. No matter. The value here is that it is showing you the objects, methods, and properties you care about to accomplish your task. If you're not familiar with them, use the Object Browser (F2) in the IDE to learn more about them and navigate the object model. Also, there's a pretty good Excel object model reference on MSDN. Or you could use the PRO*Tools COM object viewer (proobjvw.exe), if you can stand the pain.
4. Clean up the code so you've got a module that does just what you want, and no more. Like I said, the recorder will be overly verbose, especially when it comes to things like object formatting.
5. (The fun part ) Go to your ABL editor of choice and write a scratch program, using the logic from step 4 as a guide. Turn your VBA code into syntactically-correct ABL (period delimiters become colons, statements are terminated with periods, yadda yadda) and get it to compile cleanly. Don't forget the error handling!
6. Run it! You may have some fun run-time errors to deal with, with vague unhelpful error text. Fix these; the debugger is your friend.

So, back to your situation. Is it possible in Excel to create a footer that applies only to the first page? Yes, it is (I tested this in Excel 2010). YMMV. So now all you have left are steps 2-6.

Disclaimer: I'm not a coder (should be obvious ), but I play one on TV.

 

[bnstaylor]

Hi Rob,

haven't been ignoring you just that this site was down for a few days and secondly I had to figure another way of producing the so-called footer as I wanted more than just a page number, date and time and a file name - which is all EXCEL can give you.

Below is a copy of what I wanted, and have acheived, my footer to look like:

Cartons

953

Tubs

Total Weight

6070.656

NO CLAIMS UNLESS RECEIVED

1. Storage details

WITIN 48 HOURS OF DELIVERY

2. more storage details

Date of Issue

Customer No.

Ship To

Phone No.

Order No.

Terms

Operator

Run No

Drop No

Source Doc

Docket No

14/03/2011

1002917

41044

3313187

6061

10

3313187

Company Name

Account Instructions

A.B.N.:99 999 999 999

Paid:

Cheque

Cash

Total

Tub Stocktake

Tub Charges____________@_____________

Driver Signature

Credits____________@____________

Pallet Charges___________@_____________

Customer Signature

Credits____________@_____________

*03313187*

Tub instructions IN/OUT

Customer Name

The "*0331318*" is actually a barcode which prints correctly but doesn't copy and paste so well as you can see.

How did I do this?

Well all I did was to have no footer and to extend the details to print the above after a certain row number had been reached.

Your comment about COM object viewer is oh so true !!!

But I found the MSDN site very useful only it took some time to convert the statements into "Progress-speak".

Regards, and thanks, Barry.