Renaming Word Formfields: string too long error

[silverspr]

Hello everyone,
Apparently this is a known problem? String too long while renaming formfields.
Background: I have many forms which have been filled out by users, however the form design was shoddy and the fields were not properly bookmarked. I need to rename the fields/bookmarks to enable uploading the field data into an access database. The form fields are all set to unlimited text. However when using the following function, I am getting a string too long error. The fix documented at http://word.mvps.org/FAQs/MacrosVBA/...mFldResult.htm doesnt work for me. I've attached a sample file with the function below installed in the vba section. Can someone assist me with getting the function working. thanks

Option Explicit
Sub RenameFormFields()
Dim oFrmFlds As FormFields
Dim pIndex As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim oVar As Variant
pIndex = 0
i = 0


j = 0
k = 0
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect
End If
Set oFrmFlds = ActiveDocument.FormFields
For pIndex = 1 To oFrmFlds.Count
oFrmFlds(pIndex).Select
Select Case oFrmFlds(pIndex).Type
Case wdFieldFormTextInput
oVar = oFrmFlds(pIndex).Result
i = i + 1
With Dialogs(wdDialogFormFieldOptions)
.Name = "Text" & i
.Execute
End With
oFrmFlds(pIndex).Result = oVar
Case wdFieldFormCheckBox
oVar = oFrmFlds(pIndex).CheckBox.Value
j = j + 1
With Dialogs(wdDialogFormFieldOptions)
.Name = "Check" & j
.Execute
End With
oFrmFlds(pIndex).CheckBox.Value = oVar
Case wdFieldFormDropDown
oVar = oFrmFlds(pIndex).DropDown.Value
k = k + 1
With Dialogs(wdDialogFormFieldOptions)
.Name = "DropDown" & k
.Execute
End With
On Error Resume Next
oFrmFlds(pIndex).DropDown.Value = oVar
On Error GoTo 0
Case Else
'Do Nothing
End Select
Next pIndex
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
lbl_Exit:
Exit Sub
End Sub

[fumei]

1. " the form design was shoddy and the fields were not properly bookmarked"
I'll say. I suspect the formfields were copied. Do not do that! Although it could be it was not you who did it. Copied formfields do NOT copy bookmarks (the names).

2. yes, it is a known problem (and a stupid one at that).

3. the suggested solution at MVPS does work for you. It just needs to be adjusted. The key is that you action against the Range.Fields(index). So...

change (for the text formfields):
oFrmFlds(pIndex).Result = oVar

to:
oFrmFlds(pIndex).Range.Fields(1).Result.Text = oVar

[silverspr]

HI Fumei
I can't thank you enough, you have saved me a ton of "manual" copy and paste time.
thanks again
its seems to be working...

[fumei]

You are welcome. Hopefully next time it will be set up better. Again, I suspect formfields were copied and pasted. While that does put in new formfield, these do not have names (i.e. they do not have bookmarks). This makes it difficult to use VBA to point to them. They can be pointed to by formfield index number, but this is often not helpful.

Formfield index number is assigned by the creation order. It is a function of time. In a document with as many formfields as your example...who the heck is going to be able to keep track of that. I seriously doubt they were created perfectly in order, one after the other.

[silverspr]

HI I wonder if you could help one more time. The script you assisted with earlier, is being called from within Access. I can only run the routine that calls this script once. Then I get the 462 error. My research tells me word is not being closed properly however when opening task manager I can't see any word instances running. Below is the calling script with the line closing word in red:

Function OpenWd()
Dim appWord As Word.Application, strdir As String, strfile As String, strarchive As String
Dim doc As Word.Document
Dim MyDB As Database
Dim rst As Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

strTemp1 = DLookup("[sFilePath]", "tblconfig", "[description] like ""filepath temp1""")
strdir = DLookup("[sFilePath]", "tblconfig", "[description] like ""filepath strdir""")
strarchive = DLookup("[sFilePath]", "tblconfig", "[description] like ""filepath archive""")

Kill strTemp1
Open strTemp1 For Append As #1

Print #1, """" & "QID" & """" & Chr(59) & """" & "Response" & """" & Chr(59) & """" & "Reviewee" & """"
Close #1
On Error GoTo ErrorHandling

strfile = dir(strdir)

Do While strfile <> ""
strDocName = strdir + strfile

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
appWord.Visible = False
'With doc
Debug.Print strDocName
Call RenameWdFormFields 'heres where I call the script you helped with
Call GetFld(strTemp1)
'End With

appWord.Documents.Close savechanges:=wdDoNotSaveChanges
strfile = dir()
Loop

If blnQuitWord Then appWord.Quit
'MsgBox "complete"

Cleanup:
'doc.Close
Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Function

ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup

[fumei]

Are you using Option Explicit? You have undeclared variables.

I would take the createobject out of the Dir loop. You are creating it each time. BAD IDEA!!! Create ONE instance of Word, and use it. Multiple creations is just asking for trouble. ONE instance of Word is quite capable of handling multiple document files.

So do Set appWord ONCE, but Set doc multiple times.

Set appWord = GetObject(, "Word.Application")

Do While strFile <> ""
strDocName = strdir + strfile
set doc = appWord.Document.Open(strDocName)
yadda yadda yadda
doc.Close
set doc = Nothing
Loop

Each INDIVIDUAL doc is opened, actioned, closed, and the doc object set to nothing...then a new DOC OBJECT created...but only ONE Word application object (instance) is used.

[silverspr]

Excellent, you are the horse whisperer of word automation. Thank you so much again. It works like a charm and of course your recommendations were spot on. I tried making that code more efficient for at least 2 hours. It wasn't until you suggested moving GetObject(, "word.application") from the within the loop statement, so obvious but I just never made that connection.

Your were right, option explicit was not set, I've since corrected this.
The application is working "flawlessly"...thanks to you. I can't tell you how much good you and others do by volunteering your time and expertise to help out us novices.

Good day to you and thanks.

[fumei]

You are most welcome.