Mail Merge into Separate Documents - Form Text Field Attributes

[madpsychot]

I am looking for some real help here!

The scenario:

I am making report for my school, by mail merging a master report layout with data from an Excel spreadsheet. That merges the student name, and puts their correct class, teacher etc on every report. I also have Form Text Fields, for teachers to enter their comments.

I merge this master into a single document, so I end up with a document containing 360 students. This is done using the macros provided by Microsoft Support. This single document has therefore retained all the form text fields I put in, which would ordinarily have been lost.

After I have done this, I use the excellent split document macro (from this site!) to separate all my reports into one report - per student. Everything works well! So what is my problem?

Well, in my master report I have set a maximum character limit for each of the form text fields. In the mail merge this is lost, and each text field now becomes unlimited text.

In the macros provided by Microsoft Support, the macro first copies the contents of the text field, then the name of the field, and then replaces it with a placeholder. As my reports are empty to start with I have no need for the contents to be copied. Is there a way that the attribute (maximum character length) of the field can be copied and then put back in?



Sub PreserveMailMergeFormFieldsNewDoc()
Dim fFieldText() As String
Dim iCount As Integer
Dim fField As FormField
Dim sWindowMain, sWindowMerge As String
On Error GoTo ErrHandler
' Store Main merge document window name.
sWindowMain = ActiveWindow.Caption
' Because the document contains form fields it should be protected, so unprotect document.
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect
End If
' Loop through all text form fields in the main mail merge document.
For Each aField In ActiveDocument.FormFields
' If the form field is a text form field...
If aField.Type = wdFieldFormTextInput Then
' Redim array to hold contents of text field.
ReDim Preserve fFieldText(1, iCount + 1)
' Place content and name of field into array.
fFieldText(0, iCount) = aField.Result
fFieldText(1, iCount) = aField.Name
' Select the form field.
aField.Select
' Replace it with placeholder text.
Selection.TypeText "<" & fFieldText(1, iCount) & "PlaceHolder>"
' Increment icount
iCount = iCount + 1
End If
Next aField
' Perform mail merge to new document.
ActiveDocument.MailMerge.Destination = wdSendToNewDocument
ActiveDocument.MailMerge.Execute
' Find and Replace placeholders with form fields.
doFindReplace iCount, fField, fFieldText()
' Protect the merged document.
ActiveDocument.Protect Password:="", NoReset:=True, _
Type:=WdAllowOnlyFormFields
' Get name of final merged document.
sWindowMerge = ActiveWindow.Caption
' Reactivate the main merge document.
Windows(sWindowMain).Activate
' Find and replace placeholders with form fields.
doFindReplace iCount, fField, fFieldText()
' Reprotect the main mail merge document.
ActiveDocument.Protect Password:="", NoReset:=True, _
Type:=WdAllowOnlyFormFields
' Switch back to the merged document.
Windows(sWindowMerge).Activate
ErrHandler:
End Sub


This is the code that copies the contents and the form field, and replaces it with a placeholder.


Sub doFindReplace(iCount As Integer, fField As FormField, _
fFieldText() As String)
' Go to top of document.
Selection.HomeKey Unit:=wdStory
' Initialize Find.
Selection.Find.ClearFormatting
With Selection.Find
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
' Loop form fields count.
For i = 0 To iCount
' Execute the find.
Do While .Execute (FindText:="<" & fFieldText(1, i) _
& "PlaceHolder>") = True
' Replace the placeholder with the form field.
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range, Type:=wdFieldFormTextInput)
' Restore form field contents and bookmark name.
fField.Result = fFieldText(0, i)
fField.Name = fFieldText(1, i)
Loop
' Go to top of document for next find.
Selection.HomeKey Unit:=wdStory
Next
End With
End Sub


And this is the find and replace to put the Text Fields back in their places.

Thanks in advance, I do hope there is someone out there who knows how to help me!

[fumei]

If the limit is the same for each new formfield - and you know what it is - then this can be fixed very easily. Say it is limited to 10 characters:


' Replace the placeholder with the form field.
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range, Type:=wdFieldFormTextInput)
With fField
With .TextInput
.EditType Type:=wdRegularText, Default:="", Format:=""
.Width = 10
End With
.Name = fFieldText(1, i)
.Result = fFieldText(0, i)
End With


It is delimited to 10 characters.

[fumei]

BTW: it is rather odd to see a thread with ZERO responses already have 5 stars as an "Excellent" thread. Interesting. What makes a thread an "Excellent" thread...when no one has given any response?

Hmmmm?

[madpsychot]

Thanks for the reply Fumei. I'll throw that code into the the macro and see.

Annoyingly, I have 13 Text fields. The first one is limited to 1000 characters, and the rest are limited to 850 characters. Is there anyway to code the first one differently than all the others?

Thanks for you help, it's really appreciated.

[fumei]

"Is there anyway to code the first one differently than all the others?"

Yes.

[madpsychot]

Thanks for the answer Fumei! My experience of VB is roughly 2 days! I have had a crash course in what it can and can't do.

Is there any chance you'd be willing to tell me how I can set the character length different for the first text field and all the rest another length?

Thanks in advance!

[madpsychot]

Ok, I had a quick go, but with my 2 + days of VB experience I'm struggling a bit.

I took the code snippet that Fumei provided and spliced it into the Microsoft Support code. It worked a treat and now I have text fields that are restricted to 830 characters, which is perfect! But I need the first text field set to 1000 characters. I had a go at writing some VB code myself.


' Replace the placeholder with the form field.
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range, Type:=wdFieldFormTextInput)
With fField
If iCount = 1 Then
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range, Type:=wdFieldFormTextInput)
With .TextInput
.EditType Type:=wdRegularText, Default:="", Format:=""
.Width = 1000
End With
End If
With .TextInput
.EditType Type:=wdRegularText, Default:="", Format:=""
.Width = 830
End With
.Name = fFieldText(1, i)
.Result = fFieldText(0, i)
End With



Into Fumei's code I tried to put in my own condition (marked in dark red). The logic that I (wrongly) was using was that the variable iCount counts each text field, adding 1 with every subsequent text field it finds. My logic was that the IF would find text field 1, insert a text field and set it's character length to 1000.

The IF condition would end and then the script would continue looking for rest of the text fields and insert character limits of 830. Alas, it did not work.

Is there any kind soul who can point an absolute beginner at least in a vaguely correct direction!

[fumei]

1. Please use the code tags when posting code.

2. "The IF condition would end and then the script would continue looking for rest of the text fields and insert character limits of 830. Alas, it did not work. "

Not...it would not. Let's look at the logic.


If iCount = 1 Then
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range, Type:=wdFieldFormTextInput)
With .TextInput
.EditType Type:=wdRegularText, Default:="", Format:=""
.Width = 1000
End With
End If


The IF (regarding iCount) only checks ONE thing. If iCount = 1...do this.

That is all it does.

You need to add an instruction regarding if it is NOT = 1.



' Loop form fields count.
For i = 0 To iCount
' Execute the find.
Do While .Execute(FindText:="<" & fFieldText(1, i) _
& "PlaceHolder>") = True
If i = 0 Then ' i.e the FIRST one!
' Replace the placeholder with the form field.
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range, _
Type:=wdFieldFormTextInput)
With fField
With .TextInput
.EditType Type:=wdRegularText, Default:="", _
Format:=""
.Width = 1000
End With
.Name = fFieldText(1, i)
.Result = fFieldText(0, i)
End With
Else ' i.e it is NOT the FIRST one!
Set fField = Selection.FormFields.Add _
(Range:=Selection.Range, _
Type:=wdFieldFormTextInput)
With fField
With .TextInput
.EditType Type:=wdRegularText, Default:="", _
Format:=""
.Width = 830
End With
.Name = fFieldText(1, i)
.Result = fFieldText(0, i)
End With
End If
Loop
Next i


With all due respect to Microsoft, this is not the way I would do it. Using Selection.Find is...inefficient.

Using Selection in VBA code is generally to be avoided.

[fumei]

BTW: my comment regarding the five stars for "Excellent".

YOU did that. No one else could have, as there had been NO views of your thread.

Do not do this.

[madpsychot]

Thanks for the advice. The code worked exactly as I needed it to, which has really helped me in my reports. For that I am very appreciative.

Now some words of advice from me:

I have checked many of your posts on this forum. Obvious is the fact that you have tremendous knowledge in coding and problem solving. The fact that you have helped so many people is apparent just with a cursory glance over your posts.

But the other thing that is apparent is that sarcasm and a stern tone do very little if anything at all to build the confidence of people who are learning something new. In this case, the "something" is VB code, not exactly simple to anyone inexperienced in programming.

Although a technical forum, a little praise and morale boosting would do wonders. I do know what I talk about here, I have been a teacher for 12 years.

So again, thanks for advice and help, it really is appreciated. But no thanks to the sarcasm, and the condescending way in which you give it.

And yes I did put the 5 stars on the thread. It was more an accident than anything else, I don't normally post on forums, therefore don't know the etiquette involved in these matters. A simple / polite / friendly / humorous / jovial / casual "that's not the way we do things", or "please don't do that again" would be much more readily accepted than "Do not do this".

Over and out.

[fumei]

Noted. I only did that way because you never responded to my first comment about the five stars. Frankly, I find it very difficult to believe anyone could put a five star rating (on an empty thread) by accident. Oh well, I will know better next time.

Or is that being sarcastic as well?

However, truth be told, yes, I have been taken to task many times for my, ummm, sometimes insensitive tone. However, I just went back through this thread, reading every word I posted. It is unfortunate you feel...ummm, whatever it is you feel. Not good obviously.

I find it interesting that you (and others) are so quick to (legitimately) take me to task when I am (and yes I can be) sarcastic, or "stern". Yet none, including you, acknowledge when I am encouraging, supportive. It has never happened.

" a little praise and morale boosting would do wonders."

What you are saying is that I do not give any praise, or morale boosting...which is utterly false. But then, people see what they want to see.

Oh well. At least you have - I hope - got your code to work for you. With sincerely no sarcasm intended, thanks for the comments.

[Humaine]

This is just what I was after. I'm working at a school, and their report system has failed to live up to the hype. So as a last resort, they've got me generating template reports for the 24 classrooms with 650 students.

I've redesigned it for msword, and have fill it with form objects, but was having a nightmare with the text fill boxes after a merge.

This is still going to be a nightmare when we have to print these out, but at least I can now get them ready with this method you guys were discussing.

Thanks and I'll give this discussion 5 stars. :) If thats ok?

Btw, umm could I get a link to or can someone post the code to the merge document splitting.... I've used VB very little, but have managed to understand what you two were discussing.