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

[BonnyeLiz]

Newbie here with a major headache from . I have searched and searched and I'm confident and hopeful that you fine folks can help me!

I'm trying to achieve a last page footer (unable to tell how long the doc will ultimately be) that is populated by a macro. I found the solution to achieving a last page only footer by using an IF field:

{ IF { PAGE} = { NUMPAGES} {FILENAME \p} }

Now how do I code the macro so that the filename within the IF field is populated by the macro results instead?

Here is my code so far - Any help at all is gratuitously appreciated at the maximum!

[VBA]
Sub AutoNew()

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect
End If

If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If

If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If

Selection.EndKey Unit:=wdStory
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

If Selection.HeaderFooter.IsHeader = True Then
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
Else
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
End If

Selection.EndKey Unit:=wdLine
Selection.TypeParagraph
Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
caseno$ = WordBasic.[Left$](data_$, 10)
If caseno$ = "[{CASENO}]" Then
CASENO2$ = Mid(data_$, 11)
WordBasic.Insert CASENO2$
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Font.TimesNewRoman.Size = 7
End If
Wend
Close 1
Selection.MoveDown
Selection.TypeParagraph
Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
PATHANDFILENAME$ = WordBasic.[Left$](data_$, 19)
If PATHANDFILENAME$ = "[{PATHANDFILENAME}]" Then
PATHANDFILENAME2$ = Mid(data_$, 20)
' Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
' Selection.Font.Size = 9
End If
Wend
Close 1
WordBasic.Insert PATHANDFILENAME2$
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
If ActiveDocument.ProtectionType = wdNoProtection _
Then ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End Sub
[/VBA]

[Anne Troy]

Hi! If your code doesn't look quite right after posting, then just edit your post, select it all, and hit that AA button with the red X through it. That removes any formatting from pasting, and allows the VBA tags to work properly on it. Good luck, Bon!

[fumei]

Footers are part of the Section - they do not exist on their own. Yes there, of course a last page, but it will be part of the Section that contains i8t.

A lot depends on whether your document is using multiple Sections, AND if you have those sections using Different first page, and/or Different Odd/Even.

Ther is definitely a way to do what you ask. Allow me to go through what I think you want.

Regardless of the number of pages, create a separate footer for the last page. Correct?

This can be done. However, a major question is: what do you want to happen if additional text is inserted into the document?

Additional text will increase the pages, and these pages will include the footer. If additional text will never be inserted, then the solution is fairly straightforward. If additional text may ever be inserted, and you do NOT want this footer to be on any other page than the LAST page, it will be a bit more tricky. Still possible though.

Footers are part of the Section - they do not exist on their own. Yes there, of course a last page, but it will be part of the Section that contains i8t.

A lot depends on whether your document is using multiple Sections, AND if you have those sections using Different first page, and/or Different Odd/Even.

Ther is definitely a way to do what you ask. Allow me to go through what I think you want.

Regardless of the number of pages, create a separate footer for the last page. Correct?

This can be done. However, a major question is: what do you want to happen if additional text is inserted into the document?

Additional text will increase the pages, and these pages will include the footer. If additional text will never be inserted, then the solution is fairly straightforward. If additional text may ever be inserted, and you do NOT want this footer to be on any other page than the LAST page, it will be a bit more tricky. Still possible though. The following is basic - not checking or covering if additional text is inserted. It simply makes a separate section for the last page, and puts a filename field in the footer.

What it does:

1. Go to the last page - whatever it is
2. Back up one page
3. Go forward one page (brings it to the top. Finer tuning could use the bookmark range of the last page to do the same, but it is more code and just looks fancy...I skipped it)
4. back up ONE character
5. Insert a section break.
6. EXPLICITLY make sure that this new section does not have different first page or different odd/even
7. sets a filename field in the footer.

Done.
[vba]Sub LastPageFooter()
'
Dim i As Integer
i = ActiveDocument.Sections.Count
Selection.EndKey Unit:=wdStory
Selection.GoTo What:=wdGoToPage, _
Which:=wdGoToPrevious, Count:=1, Name:=""
Selection.GoTo What:=wdGoToPage, _
Which:=wdGoToNext, Count:=1, Name:=""
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.InsertBreak Type:=wdSectionBreakNextPage
With Selection.PageSetup
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
End With
With ActiveDocument.Sections(i + 1).Footers
.Item(1).Range.Fields.Add _
Range:=.Item(1).Range, _
Type:=wdFieldEmpty, _
Text:="FILENAME \p "
End With
End Sub[/vba]

[BonnyeLiz]

Hi Gerry!

Yes, additional text will most often be entered into the document. I've considered the bookmark part, as I think it would be easier to tell the macro to place the text at a bookmark in the last page footer. I have developed a similar code to that you offered as well, but I can't seem to link it to the text created by the macro and only to populate the footer of the page number that equals the number of pages.

I've gone the route of creating a new section on the last page of the document (NOT relating to any other header or footer that could be in place elsewhere in the doc) and inserting a bookmark for the text placement. The filename will actually be derived from the macro that runs in the code I initially posted. Have I confused you enough yet? LOL!

[fumei]

Actually, there was some errors in the code I posted. here is the corrected code:
[vba]Sub LastPageFooter()

Dim i As Integer
i = ActiveDocument.Sections.Count
With Selection
.EndKey Unit:=wdStory
.GoTo What:=wdGoToPage, _
Which:=wdGoToPrevious, Count:=1, Name:=""
.GoTo What:=wdGoToPage, _
Which:=wdGoToNext, Count:=1, Name:=""
.MoveLeft Unit:=wdCharacter, Count:=1
.InsertBreak Type:=wdSectionBreakNextPage
.MoveRight Unit:=wdCharacter, Count:=2
With .PageSetup
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
End With
End With
ActiveDocument.Sections(i + 1).Footers(1).Range.Select
Selection.HeaderFooter.LinkToPrevious = Not Selection.HeaderFooter. _
LinkToPrevious
With ActiveDocument.Sections(i + 1).Footers
.Item(1).Range.Fields.Add _
Range:=.Item(1).Range, _
Type:=wdFieldEmpty, _
Text:="FILENAME \p "
End With
End Sub[/vba]

OK"

1. the filename part is incidental. It is either a string derived from the macro (it writes a new filename...whatever), or a field that picks up the filename. If you are using the Field, then it will be the current filename. If it is derived from the macro, you must set that file save before you have the field.

2. Conceptual, there is NO such thing as a "last page footer". There is no such beast. Footers are ONLY objects of the Section. They have no other home.

3. Every Section has three (3) footers, regardless of whether you explicitly set them, or not.

4. If you run ANY macro that creates a footer on the current last page - it will do that. But if ever afterwards, that page becomes NOT the last page (by inserting additional text). that fact is irrelevant to the footer. Footers are part of the Section.

5. A bookmark put into the last page footer will work, but again, if additional pages are added, it will NOT affect that bookmark. Bookmarks are range objects, and the range location will remain. Again, if that bookmark was inserted into the footer of page 30 (the last page of a 30 page document), and the footer field set, if you add 5 pages...that bookmark will still be on page 30.

All that being stated, it is still possible to do what you want. it requires a lot more code though.

My questions to you are:

a) why do need to do this?
b) is it worth the effort?

However, in case it is.

declare a public variable OnLastPage as boolean

in a sub:
check if there is a section break just before the last page
if True,
check the footer of that last section
for a bookmark LastPageBM
if true
check the contents and update field if needed
if false
check the contents
delete things if needed
call separate sub LastFooter
if false
search make for a previous section break
if found
check footer contents
if contents are field filename
go to section break, back up
delete section break
call lastfooter sub
if contents are NOT empty AND NOT field filename
**** you need decision here
if NOT found
call LastFooter


LastFooter = essentially the code I posted above.

[TonyJollans]

Hi BonnyeLiz,

Welcome to VBAX!

Gerry is more expert than I in setting up the various footers in multiple sections and you would do well to follow what he says.

However, I have a couple of comments:

1. You seem to scan the same file twice which overcomplicates the code.

2. Don't use WordBasic except in the very occasional cases where it provides facilities not available any other way.

3. Your code is in an AutoNew Macro and only runs when a new document is created, so you are setting up all your footers before doing much else. At this point you don't know what else will be in the document. Will there ever be sections added? (A variety of formatting can add sections without you always realising it).

4. The good news! The field you first posted will, effectively, give you a last page footer if it is in the footer which happens to be on the last page; if it is in any other footer it will print nothing so perhaps Gerry, too, is overcomplicating things slightly. Code for adding nested fields is a little awkward but it can be done.

[BonnyeLiz]

To answer your questions... It was discovered during my contract that I could bend MS Word to will by VBA code. Folks here have slowly (and still are) migrated/migrating from WordPerfect. When I came along and showed them how to do different forms using code to make it easier on the user, they were thrilled. Unfortunately, since the discovery that I could make some things "special" for them - they want more, and I have less time to make it so.

Until now, the filename macro has been good enough. It has posted to every page in the document, in the footer. When I created protected forms for this particular area with the same code snippets in place and moving the filename/number off the first page- they decided they wanted on the last page only.

Believe me, the last two weeks I have been banging my head about this have made me wonder if it was worth the effort. If these people were not of such strong importance to this particular organization, I probably wouldn't have placed such an extreme effort. But, let's just say it is governmental level higher ups that are asking me to do this.

I've hit another brainstorm on this too - after reading through your code, Gerry. Wouldn't it be simpler to install a macro button on users' toolbars that launches the code? I'm not sure I can get them to go for that, however. They've been so used to doing nothing and getting this footer. Currently their answer to my inquiries is "how about examples of each method you're suggesting?" Arrrrgh!

Reading Tony's post gives me some hope too! I have it running through the AutoNew because the document is based on a template. It's the only way that their 3rd party software can integrate properly. I don't expect any section additions from the users, as this is also a protected document.
So it IS possible to code in nested fields??? I have searched through the object library trying to find something there that would indicate this. Do you have an example of how I would code nested field insertion that you would be willing to share?

I'm so appreciative of all this help in so many ways. It's allowing me to step back and take on new perspectives as well. Thank you, thank you, thank you!

BonnyeLiz

OK Gang - I am going to slap myself if it is this easy. Someone else just offered this suggestion:

"Assign the name to a document property for example the title and then insert the document property using insert field into the header/footer.

ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = ManualTitle$"

So I assign the macro output text to a document property and then simply insert the If Field in the footer designating the document property as the text to be inserted? Is this really going to work??? I'm going testing, gang - give me your thoughts in the meantime. I'll let you know what I discover!

Bonnye

[TonyJollans]

Yes, Bonnye, using a Document Property should work and makes the coding easier . You an just put all your Fooers as you want them in the Template without using code at all.

[BonnyeLiz]

I tweaked it a little, and it did not test successfully

Is there something so very obvious here that I am just not able to see? I figured that since it is a protected document, I should still seek the footer in the code? Even with the Header/Footer code removed - what am I doing wrong???

[VBA]
Sub AutoNew()
Dim strCase As String
strCase = CASENO2$
Dim strPath As String
strPath = PATHANDFILENAME2$

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect
End If

If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If

If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If

Selection.EndKey Unit:=wdStory
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

If Selection.HeaderFooter.IsHeader = True Then
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
Else
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
End If


Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
caseno$ = WordBasic.[Left$](data_$, 10)
If caseno$ = "[{CASENO}]" Then
CASENO2$ = Mid(data_$, 11)
WordBasic.Insert CASENO2$
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Font.Size = 7
End If
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = strCase
Wend
Close 1

Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
PATHANDFILENAME$ = WordBasic.[Left$](data_$, 19)
If PATHANDFILENAME$ = "[{PATHANDFILENAME}]" Then
PATHANDFILENAME2$ = Mid(data_$, 20)
End If
ActiveDocument.BuiltInDocumentProperties(wdCurrentFolderPath) = strPath
Wend
Close 1

ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
If ActiveDocument.ProtectionType = wdNoProtection _
Then ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End Sub
[/VBA]

New eyes could possibly see my simple errors :::crossing fingers:::
Thanks so much for looking!
Bonnye

[sandam]

What goes wrong? What sort of errors do you get? Which lines are highlighted when you click DEBUG?

[BonnyeLiz]

I receive no errors when debugging. However, when I run the macro within the document, I get "runtime error 13 mismatch" once it reaches the following line:

[VBA]

End If
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = strCase
Wend
Close 1


Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
PATHANDFILENAME$ = WordBasic.[Left$](data_$, 19)
If PATHANDFILENAME$ = "[{PATHANDFILENAME}]" Then
PATHANDFILENAME2$ = Mid(data_$, 20)

End If
ActiveDocument.BuiltInDocumentProperties(wdCurrentFolderPath) = strPath
Wend
Close 1

ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

If ActiveDocument.ProtectionType = wdNoProtection _
Then ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

[/VBA]

I remain.... clueless

[TonyJollans]

Hi Bonnye,

Sorry for not replying earlier. I was out last week and did have a look at this when I came back but couldn't make it work as I wanted so left it, intending to return. Anyway ...

I don't see why you should get a type mismatch setting the property but if we try and simplify it perhaps we can sort it out.

To begin with:

(a) Set the Title Property of the Template blank.
(b) Forget the second string you want to have in the Footer.

and then, in your template footer put the fields:

{ IF { PAGE } = { NUMPAGES } { DOCPROPERTY TITLE } }

(and set it to whatever font and size you want)

You do not, then, need to edit the footer in your code (actually it would be nice if you could but I couldn't make it work no matter what I tried) so a lot of the code can be removed, leaving ..

[VBA]Sub AutoNew()
Dim strCase As String
strCase = caseno2$
Dim strPath As String
strPath = PATHANDFILENAME2$

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
ActiveDocument.Unprotect
End If

Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
caseno$ = WordBasic.[Left$](data_$, 10)
If caseno$ = "[{CASENO}]" Then
caseno2$ = Mid(data_$, 11)
WordBasic.Insert caseno2$
Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
Selection.Font.Size = 7
End If
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = strCase
Wend
Close 1

If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If

End Sub[/VBA]

Now, the code at the beginning sets strCase to caseno2$. Is this a global variable? And if so, what value does it have? I suspect you don't want that line. Next you declare and set variable strPath which you don't use. Let's delete those three lines for now.

Next, removing protection. There's no point in having protection on in the template if the first thing you do when you create a document based on it, is to remove that protection so make sure protection is not on in the template and then we can remove this code as well.

Now the loop processing the file. Because you are now using a pre-formatted field in the footer you no longer need to insert and format the text, so that's more code can go!!

Lastly, for the moment, you set (or you try to set) the "Title" document property to the variable strCase. You'll remember we removed the line setting that variable at the top, so it is now uninitialised. What I think you want is the value read from the file which you have in variable caseno2$, so let's use that for now - and as you only want to do it once, let's move it inside the IF block.

What is now left is this:
[VBA]
Sub AutoNew()
Dim strCase As String

Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
caseno$ = WordBasic.[Left$](data_$, 10)
If caseno$ = "[{CASENO}]" Then
caseno2$ = Mid(data_$, 11)
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = caseno2$
End If
Wend
Close 1

If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If

End Sub[/VBA]

Now we can begin to see the light! Although there's nothing wrong with using them, the variables caseno$ and caseno2$ don't really add anything as each is only used once so, for now at least, lets replace them with substring functions and, while we're at it let's get rid of the archaic WordBasic stuff - and also the redundant declaration of strCase. What's left? Well, this:
[VBA]
Sub AutoNew()

Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
If Left$(data_$,10) = "[{CASENO}]" Then
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = Mid$(data_$, 11)
End If
Wend
Close 1

If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If

End Sub[/VBA]

Now I have one problem (as I hinted near the beginning of this ramble) - I don't seem to be able to make the field update from the autonew macro and you won't be able to do it manually if the document is protected, so just for testing, can you comment out the setting of protection at the end of the code, like this:
[VBA]
Sub AutoNew()

Open "C:\cycomdat\lh_macro.txt" For Input As 1
While Not EOF(1)
Line Input #1, data_$
If Left$(data_$,10) = "[{CASENO}]" Then
ActiveDocument.BuiltInDocumentProperties(wdPropertyTitle) = Mid$(data_$, 11)
End If
Wend
Close 1

'If ActiveDocument.ProtectionType = wdNoProtection Then
'ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
'End If

End Sub[/VBA]

Try running this. It will not actually show the value you want in the last page footer right away but you can manually fix that for now by switching into Print Layout View and back to Normal View (or vice-versa depending on where you start). And then switch on your Form protection.

If that works, we can build on it. if not we should be able to easily isolate the problem. If you've managed to read this far you have dedication if nothing else

[BonnyeLiz]

Hi Tony,

I'll give this a try today and let you know! I appreciate you walking me through the changes step by step - that really helps me a lot! The code has been changed so many times since the start it is so helpful having another pair of eyes take a look at it. I'll be back to let you know what happens with it later today!

My profound thanks to you!
Bonnye

[BonnyeLiz]

Still nothing. When I run the code, nothing populates where the "IF" field is located in the footer. When I ran a preliminary variation of the code in your reply, I ended up with the IF field being populated "Error! No bookmark defined."

I placed the IF field: {IF {PAGE} = {NUMPAGES} {DOCPROPERTYTITLE}
in the footer accordingly. I assured that the document property title was also blank, as you mentioned.

So why is it that the document property title field is not retaining the variable the code gives it?



Still dedicated to solving this mystery!
Bonnye

[TonyJollans]

Hi Bonnye,

First, to see if the property is set:

After running your code on a new document, check the title via File > Properties > Summary tab

If it is set, then the field isn't picking it up so double check the field, make sure you've entered it correctly - I know you can't easily cut and paste it to here but what you've typed is invalid.

Let me know. Meanwhile I'll try and mock up a template and post it for you.

[BonnyeLiz]

Hi Tony,

LOL - I am in the midst of doing the same - making a mock template with simpler variable determination to the "title" property. One that doesn't call the external information, just so I can try to make the text I want to appear as the property title. We shall see!

I went back to the properties of a new document after running the code, and the title still shows blank under the summary tab.

I'm not certain it matters, but the code that calls the caseno2$ information is affiliated with a third party software that assigns file numbers as part of case management. That's all it is, but it is not a variable that can be plucked from Word itself.

Still charging at it like a bull ~
Bonnye

[TonyJollans]

Hi Bonnye,

Not sure about the third party product - are you actually interfacing to it in the process of creating a new document other than by reading the file?

Anyway, here's a basic template which creates a basic footer.

[EDIT}
Just re-read your post and if the title is blank, are you sure there is valid data in your text file?
[/EDIT]

[BonnyeLiz]

I appreciate all of the assistance and efforts from all of you! I have finally moved away from this. Regardless of everything I've/We've attempted, it just isn't working. The timeline is too critical to fight with this method any longer, I'm afraid.

I've convinced them to simply allow the information on the second and subsequent pages in lieu of the final page of the document alone. I suggested a macro button assigned to their toolbars as well. They seem to be moving away from the document protection as well, which should make implementation of certain manuevers a bit easier.

Again, I applaud you all for being so helpful and allowing me to pick your brains! Until my next VBA/Word crisis, I remain...

Appreciative,
Bonnye

[fumei]

I have been away...could you please state the needs requirement again?

If I understand it:
1. to have a separate footer for WHATEVER is going to be the last page.;
2. to adjust (that is, remove) any PREVIOUS last page footer, if any change in text content changes what is the last page.

If this is correct, please confirm requirements, because I believe there is a relatively simple way to do this.

[BonnyeLiz]

Hi Gerry,

There is a Word document that needs a footer on the last page ONLY which needs to include a File number (created by Third Party Software) that is generated by a macro within the document. The filename and path are easily generated by using the IF field inserted to only appear of the page number equals the numbr of pages. There is no certainty of how many pages long the document will be.

The last attempt made was to assign the value created by the macro (filenumber from third party software) to a property within word, and then use the same IF field code to generate the information. I was never able to get that to work successfully.

If you have any other suggestions, they are most certainly welcomed!

Bonnye