Solved: Pls Help! Achieve IF Field through VBA code?

[fumei]

OK, let me see if I understand correctly.

You need a single macro that both gets a string from someplace else, AND inserts it into the last page ONLY.

It is that last page ONLY that can be the problem. As I previously posted, there are three footers in each section. Say you have NO section breaks, and you have code that displays the text of each footer in that section. Even though you think you have only one footer, you will get THREE messages, because there are always three footers in every section.

If the requirement is to have it ONLY on the last page, there is no way around this issue. You must make the last page a separate section.

This problem would not be a problem at all if there is not a requirement for the footer to be on the last page ONLY.

Just to demonstrate. say you have a document, five pages long. You make no adjustments to page setup. No different odd/even, no different first page. You put into the footer the text "whatever it is".

If you run code that checks the text of each footer in the section and performs an InStr to see if the footer text contains "whatever". It displays a messagebox with the result, AND the index number of the footer.

You will get THREE message boxes. The first will be 11 - the first 1 indicating the position of the searched for string. "whatever" is at position 1 of "whatever it is". The second 1 is the index of the footer.

The second message will be 02. The 0 is the result of the search. It returns a 0 because the second footer text does NOT contain the searched for string. The second footer does NOT have any text at all.

The third message box will be 03 - for similar reasons.

This is very important, because if you look at the document, there is footer text on EVERY page. However, technically, the second footer object in the Section has NO text, as does the third footer object.

So. If the requirement is it must be on the last page ONLY, and the number of pages can change (and that seems to be a given), you MUST search back through all the footer objects for something that identifies a previous inserted filename.

I am confused with post that refer to templates, and AutoNew routines.

PLEASE - spec out exactly what you want to happen.

Is this a new document?
Is this a previous saved document that may get a new filenumber, and therefore will be saved as a new filename?
Does it have to be on the last page?

As much as Tony may think I possibly make things too complicated (and he is absolutely correct in that sometimes), if the requirement is for:

1. the search for, and removal of, a previous footer containing an old filename;
2. a RE-Sectioning to maintain a separate footer for ONLY the last page;

Then it IS complicated. Do-able, but complicated.

Just to be clear, if you have a IF field in a footer, and you add 20 pages, that if field is still back there - especially if there are new sections.

So what I need to know is:

Is this for a template that will be making a new document, getting a new filename filenumber, and putting it into the last page footer...and is a ONE SHOT DEAL;

OR, is this a document that may change;

OR is the first, but ALSO may change.

Talk to me. I think this can be done. However....uh, you have two days. I fly out early Saturday, and will be mostly unavailable for two weeks. Doing a photo shoot in the redwoods of Northern California. Mmmmmm, yum, cool, fog, and wet.

[MOS MASTER]

Hi Gerry,

Also had a think about the subject.
Indead it seams like an interessting task to do this by code.

For me the big questione is when to execute the code..
This talk about eventhandlers that start at creating ore opening a document didn't get me closer to the sollution.. (Users can still do a lot to change the outcome afterwards)

If the procedure had be executed on a print event or on a save event then I think it would be much easier...(For the basic reason that you don't have to worry about al that extra pages a user could possibly create)

But I was wondering bonny?
The sample Tony made for you...what didn't work quite right for you?

I've been playing with it and it seams to work well? (What am I missing)

Gerry...did you look at Tony's example? Did you find anything not working there...It still seams a simple ellegant sollution.

When adding pages to the example the footer shifts nicely so...I thought that had to do it for Bonny...

[BonnyeLiz]

The sample that Tony provided worked wonderfully during my testing of it as well... UNTIL I tried to run it through with the third party software. The field showed nothing - just blank.

When using other text, it worked. I even tested the macro alone to assure that accurate data was being passed through to the document. Without the field, it places the file number in the footer. When I applied the sample, blank.

I don't understand it either, and couldn't see wasting anymore of Tony's time on it, as he was so kind and helpful. I have to assume that it has something to do with the parameters involved in the third party software. I never received an error running the code using the third party macro, but no data was produced in the document either.

The office the project is for seems to be happy with another template that is no longer protected, and inserts the file number at remaining pages after page one.

Finally admitting to being stumped, I just implemented the next best and most plausible idea for them. I KNOW that this would work if it weren't for the data needing to come from this third party software!

Bowing to all of your greatness~
Bonnye

[TonyJollans]

Hi Bonnye,

The source of your data as far as Word is concerned appears to be a text file. That text file may have been created by a third party app but that shouldn't make any difference. I'm afraid I'm at a loss at the moment to further explain what the problem may be although I'm happy to carry on looking at it if you want me too - and I'm sure others here are too.

[MOS MASTER]

The sample that Tony provided worked wonderfully during my testing of it as well... UNTIL I tried to run it through with the third party software. The field showed nothing - just blank.

Hi,

Ah ok...really thought I was losing my mind figuring out what could be wrong with the thing..

Like Tony says: Is the Third party app only providing the txt file or is it also automating this particuler template? If no automation is done by that app It shure seams strange that it's not working.

Off course it's also possible that this is an version related problem. Perhaps you can provide us with the destination version off Word the end users are running. (97,2000,2002 or 2003?)

I'd like to test it aswell in that specific version off Word.

To be precise on what to test are you using the same sample as Tony's example?

See Yah!
Ps..happy for you the customer is satisfied with the alternative..

[fumei]

OK. I was away again. Right now I am on a friend's computer, but I will be in the office tomorrow (Monday). I have a complete solution for you.

It does not use docvariables

It makes NO difference the current situation. It does:

check the cause of the last page = is it a page Break, a Section break or just pushed over into a new page by text.

It removes all existing footers that contain the document path and filename.

It forces the last page, and ONLY the last page to have a footer with the document name and path. It does not use any flelds, or an IF test.

You can add as much text as you like., anywhere. You can remove any text anywhere. Run the code and it always fixes it that the last page has the correct footer. Regardless of existing situation. Works perfectly. I will post in a few hours. need to try and get SOME nap.

[fumei]

Here is a solution that can be adjusted to accepted any string to be placed in the footer of the LAST page, and ONLY the last page. It is fully error trapped to make any adjustments for movement of the last page. Adding text to make a new last page is fully taken care of.

It does not use any IF fields, docvariables, or any other fields.

What it does:

1. goes to the last page.
2. set the Selection.SetRange to the VERY LAST character, just before the start of the last page.
3. performs tests on the next character.
LOGIC: the last page can BE the last page for three reasons.
a) an explicit Section break
b) an explicit page break
c) a DERIVED page - that is, it is calculated by Word because the text simply went on past the end of the previous page.

4. a) if there is a section break, then the last page is already in its own section;
4. b) if there is a page break (using a Ctrl-Enter) move forward and make a section break, move back and delete the page break; 4. c) if there is not a break, insert a section break

5. perform check of all footers searching for the string. If found, delete all footers with that string. All other footer text is ignored. This could be adjusted if you state requirements to do so.

The string is, in the attached file, the document path and file name - using ActiveDocument.FullName. This can be easily adjusted to pick up whatever you want. If you have a routine that determines the string by a third party, that is OK, as long as you get a string. You could save the file with a SaveAs, then use the attached document code to simply use the filename.

6. insert the document fullname (path and filename) into the footer of the last page.

As stated, to maintain ONLY the last page having the footer, you MUST make the last page into its own section. This does. I have it running on Document_Close so you can add, delete what you want. Put 400 pages after the previous last page - don't matter. It will make whatever is the last page (and ONLY the last page) have its own footer with the document path and filename in it.

The document attached is currently empty of text. Test it out. The code is commented fairly well, so you should have no problem figuring it out. It could easily be adjusted to take an input string for whatever you want to go into the footer. It is set, right now for path and filename.

Please let me know if you have further requirements that you have not stated.

could help if I attached the file, wouldn't it?

[BonnyeLiz]

Wow! I'll test this out against the third party software that inserts the information. My sincerest thanks to you!

I'll let you know how it goes!

Bonnye

[MOS MASTER]

Hi Gerry,

Nice clear coding works well!

But I still find it strange that Tony's example didn't work...it works over here...(Glad to the field feature was also nice) Oh well...can't understand everything...

[fumei]

Hi Joost!

Actually Tony's example should work, at least generically. Although his example did not do the conditional checking for the last page generation. The only thing really different (other than that checking) is that mine will take any string. That being said, mine does not truly deal with the third party factor either. I am still unclear how that works. But, as long as you can get a string out of it, the code will do what appears to be the requirement.

Thanks for the compliment. Much appreciated.

[MOS MASTER]

Hi Gerry,

Ah ok..thought I was losing my mind there.
I'm with a friend on a telephone line (Stone age) now so I'll keep it brief..

As I recall (no time to test) Tony's example worked live meaning if you added a page the last header would still be the only one being filled..(But like I said the topic's been out off my head for a week so..)

Will test more tommorow!

And for your work:....this is always clearly explained/well motivated and most important easy to read! (That's what I like...readable Code)

Talk to yah....

[fumei]

We all secretly like readable code. Most of us are too lazy to write it. Although....Tony is a master, and I unabashedly bow in his direction. Ok, maybe not as commented as I like, but even masters have their quirks.

The reason I like clear readable code is...I had to figure out over 100,000 lines of jumbled, uncommented code once (COBOL). It literally gave me nightmares.

BTW: I think there may be an error in the file I uploaded. Not sure, and I have not had the time to check. There may be an incorrect return on one of the functions, depending on what is causes the last page to BE the last page.

[MOS MASTER]

We all secretly like readable code. Most of us are too lazy to write it. Although....Tony is a master, and I unabashedly bow in his direction. Ok, maybe not as commented as I like, but even masters have their quirks.

The reason I like clear readable code is...I had to figure out over 100,000 lines of jumbled, uncommented code once (COBOL). It literally gave me nightmares.

Ah glad we like the same thing!

I to have had a simular encounter (to many times) with someones code who was very fond of not declaring variables and to make things worse liked 1 letter variables like: X = bla, y = bla, etc...(A Nightmare if the code is long and no comments are present)

After doing that my coding got a lot better from looking at it from another person's point of view (I'll let someone else decide if my code is ok or not)

As a matter off fact I do like Sir Tony's code as much as you do!

BTW: I think there may be an error in the file I uploaded. Not sure, and I have not had the time to check. There may be an incorrect return on one of the functions, depending on what is causes the last page to BE the last page.

Ah..this could be had a quick spin at it and have not run it through the debugger...(worked well as I recall and like I said love the coding)

If the OP has problems with it there's time enough to fix it!

[fumei]

Undeclared variables.....a curse. Hate them. Pure laziness as far as I am concerned. Or a arrogance of one's memory ability, or perhaps an total un-empathetic understanding that someone else is not a mind reader.

Mind you this was in the days when we did not have the luxury of using nice, long, fully named variables. So yes, lots of "x". The worst was using the SAME variable name in different procedures, but for DIFFERENT data types.

i As Integer is pretty common - still do it myself. A habit. But these guys sometimes used i As String....?????? Can we say consistent?

[MOS MASTER]

A big Amen to you're comments....(and i do use a simple i for a counter also from time to time or an l if it needs to be long)

But normally when I review codes I change to naming conventions and change to: lCnt or iCnt (Or for those who are in to 3 ltr prefixes: lngCnt or intCnt)

Cheers!

[TonyJollans]

Gerry, Joost,

Thankyou for the kind words about my coding. You are both excellent coders and praise from you is praise indeed.

It is probably true that my code sometimes lacks sufficient comments - I will try and do better

To take the thread off topic for a moment - it's funny you should mention Cobol, Gerry, - my son who is a web developer is amused by the fact, as he sees it, that my HTML looks like Cobol - I wonder if my VBA does too! Just for fun (well, there was a serious point to it) I once wrote a C program which looked exactly like Cobol - I forget the details now but I defined Cobol keywords as C elements (#define Identification Function Main(), etc. - or something of the sort) and if memory serves I couldn't quite get the same source to do the same thing run through the two different compilers but it was very close.

[MOS MASTER]

Gerry, Joost,

Thankyou for the kind words about my coding. You are both excellent coders and praise from you is praise indeed.

It is probably true that my code sometimes lacks sufficient comments - I will try and do better

You're welcome Tony,

As for the lacks of sufficient comments I think we all fit that glove!
I hope I will get to the stage of always writing comments in my code (and hopefully more good habbits) even when replying to questions (in those small codes I usually forget it)

I'll leave the Cobol bith to Gerry...I know what it is, but that says it all!

[fumei]

Ah...back when in the days when that kind of stuff was what we did for fun!

[MOS MASTER]

Ah...back when in the days when that kind of stuff was what we did for fun!

haha...yes time for tinkering seams less and less, but when it's there it's still so much fun!

[BonnyeLiz]

OK - Let's mark this solved! All the hardwork and dedication paid off tremendously and for ALL of your efforts, I am gracious

Until next time!
Bonnye