Excel VBA, how to retrieve textbox title/label in word fillable form

[chieh]

Hi All:

I have multiple word fillable form, I need to retrive all the value filled in by the user on each form and insert the value into Excel sheet row by row, each row represents 1 file.

I got the program working using Excel VBA, however I try many ways to get the title or label that's on top of each textbox field.

i.e.
First Name [Chieh ], Last name [Zh ], Address [123 confused street ], ... .... ...
I can get the textbox type, name and result using following command

'Retrieve Full Filename, field type, field name and fieldvalue
strfieldtype = oDoc.FormFields(i).Type
strFieldName = oDoc.FormFields(i).Name
strFieldValue = oDoc.FormFields(i).Result

How do I retrieve the label "First name", "Last Name".... I tried to search through the word object, with no success.... Kindly help.

Thanks
Chieh

 

[Andrzejek]

If it is a Label, try:
[tt]
strField[blue]Caption[/blue] = oDoc.FormFields(i)[blue].Caption[/blue]
[/tt]
Just a guess here....


Have fun.

---- Andy

 

[chieh]

Hi Andy:

No, there is no .Caption property or method, I tried to look through whole word object and still couldn't find it.

Regards,
Chieh

 

[macropod]

Hi Chieh,

If you know what the fields are, why do you need to retrieve what, from your description, is not a part of those fields?

FWIW, formfields in Word have a bookmark property. If you assign bookmark names to the formfields you can reference them for data retrieval via the bookmark names rather than the index #s. For example:
ActiveDocument.Bookmarks("Name").Range.Fields(1).Result
Alternatively, if you reference them via the index #s, you can retrieve the bookmark names. For example:
ActiveDocument.FormFields(3).Range.Bookmarks(1).Name

Cheers
Paul Edstein
[MS MVP - Word]

 

[chieh]

Hi Paul:

I got the program working using Excel VBA (I paste the full program code at the end), the info I try to get is the
title or label that's on top or beside each textbox field.

For Example, in my word form, there are following field:
First Name [Chieh ], Last name [Zh ], Address [123 confused street ], ... .... ...
I can get the textbox object type, object name name and result using following command

'Retrieve Full Filename, field type, field name and fieldvalue
strfieldtype = oDoc.FormFields(i).Type
strFieldName = oDoc.FormFields(i).Name
strFieldValue = oDoc.FormFields(i).Result

But can't get the caption, i..e "First Name", "Last Name", "Address"...
I did try the following.

strCaption = oDoc.Bookmarks("Last Name").Range.Text
the value I got is a blank box... i.e. ""
there is 45 formfields in my form, all 45 of them shows this blank box.

I am trying retrieve this caption to use as dynamic column heading in Excel as I am retrieving word form data.

At the is point, I use the FormField(i).Name as dynamic heading for my excel spreadsheet, the program works, but have no success in getting the caption.

My first question is: How do I retrieve the label "First name", "Last Name".... I tried to search through the word object, with no success.... Kindly help.

My second question is. when I ran through my excel vba, 1 or 2 times out of 10, it crashes in the middle. the error messge is " -2147417851: Method 'Item' of object 'FormFields' failed ", any idea on this error? you can copy my code to your local and test it with few word form.

I am fairly new to VBA, eventhough I tried my best to code the program without any hard-coding, I am sure there are improvement can be added, Please feel free to review my code and add, change, modify or suggest for improvement to the code. It would be very helpful for me to gain some expert knowledge and advice from you all.

I am using office 2003

Thank you.
================================================================

Sub WordExtract()
'==
'Add Word object reference library.
'Tools->References - Check the Microsoft Word Object Libary box
Dim wbWorkBook As Workbook
Dim wsWorkSheet As Worksheet
Dim oWord As Word.Application
Dim WordWasNotRunning As Boolean
Dim oDoc As Word.Document
Dim varFileName As Variant
Dim intHeaderRow, intNumberOfField, i As Integer
Dim strPath, strDocFiles, strDisplayText, strFullName, strFieldName, strFieldValue, strCaption, strTempFieldValue As String
Dim wsMessage
Set wsMessage = CreateObject("WScript.Shell")


Set wbWorkBook = ActiveWorkbook
Set wsWorkSheet = wbWorkBook.Worksheets(1)
Range("A1").Select

'For FYI Info....
wsMessage.Popup " This Utility Only Works with *.Doc Files, Not the *.Docx, Press OK To Continue.... ", 5, _
"..... Information .....", 4096

'Get existing instance of Word if it's open; otherwise create a new one
On Error Resume Next
Set oWord = GetObject(, "Word.Application")
If Not Err Then
'Close the word instance if open
oWord.Quit
End If

Set oWord = New Word.Application
On Error GoTo Err_Handler

'Prompt user for the directory where all the word document are located.
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 1 Then
strPath = .SelectedItems(1)
End If
End With
If strPath = Empty Then
wsMessage.Popup "No folder Selected", 5, "..... Error .....", 4096
Exit Sub
End If

'Get the last row
xRow = wsWorkSheet.Range("A65536").End(xlUp).Row
'Append new Row.....
xRow = xRow + 2

'Keep track number of word document processed
intFileProcessed = 0

'Retreive list of all the word doc files in the given directory
'For now this only works with *.Doc files only. Not the *.Docx, as we only have word office 2003 installed.
'It can be change easily to accomodiate the new docx format.
strDocFiles = Dir(strPath & "\*.Doc")

' Loop through all the word document in this directory, retrieve the info and insert it into the excel sheet.
Do While strDocFiles <> ""
intFileProcessed = intFileProcessed + 1

'Prompt to select single file
'varFileName = Application.GetOpenFilename("Word Files (*.doc; *.docx), *.doc; *.docx")
'varFileName = Application.GetOpenFilename("Word Files (*.doc; *.docx), *.doc; *.docx", , , 1)
'Prompt to select a directory (More than one word file)
Set oDoc = oWord.Documents.Open(strPath & "\" & strDocFiles, Visible:=False)

With wsWorkSheet
'Get the Total Number of user fillable TextBox field in this Document
intNumberOfField = oDoc.FormFields.Count
'Get the Full Name of the current word document
strFullName = oDoc.FullName

'Display processing info in the popup window.....
strDisplayText = "Processing..... " & strFullName & " - Total Field Count: " & Trim(Str(intNumberOfField))
wsMessage.Popup strDisplayText, 1, "Processing", 4096

'If this is the first file being processed, retrieve the header too..
'At this point, haven't figure out how to retrieve the textbox title/label, so just retrieve the actual object name
If intFileProcessed = 1 Then
'Loop through all the fillable fields
For i = 1 To intNumberOfField
strCaption = ""
'Retrieve field object Name and insert/update into Excel cell
strFieldName = oDoc.FormFields(i).Name
'The following commented out line: Trying to get the field caption, didn't work....
'strCaption = oDoc.Bookmarks(strFieldName).Range.Text
'.Cells(xRow, i + 1) = strFieldName & " - " & strCaption

wsWorkSheet.Activate
.Cells(xRow, i + 1) = strFieldName
Next i

'Save the header row # for setting them to Bold after all the files is run.
intHeaderRow = xRow
'Add date and time stamp to first column in the header row
.Cells(xRow, 1).Select
Selection.Value = Now()
Selection.HorizontalAlignment = xlLeft
End If

'Append new Row.....
xRow = xRow + 1

'Update the full name of the word doc in the first column of the current row
wsWorkSheet.Activate
.Cells(xRow, 1) = strFullName

'Retrieve the fillable field result for the current document.
For i = 1 To intNumberOfField
'Retrieve Full Filename, field type, field name and fieldvalue
strfieldtype = oDoc.FormFields(i).Type
strFieldName = oDoc.FormFields(i).Name
strFieldValue = oDoc.FormFields(i).Result

'Display processing info in the popup window.....
'strDisplayText = "Processing..... " & strFullName & " - Total Field Count: " & Trim(Str(intNumberOfField)) _
'& ", _Current Field: " & Trim(Str(i)) & " - " & strFieldName & ", Field Value: " & strFieldValue
'wsMessage.Popup strDisplayText, 1, "Processing", 4096

' Type "wdFieldFormCheckBox" = 71, if it's a check box, the value store is either "1" or "0" for true or false
' the following converts "1" to "True" and "0" to "False" for easier understanding by the users.
If strfieldtype = 71 Then
Select Case strFieldValue
Case "0"
strTempFieldValue = "No"
Case "1"
strTempFieldValue = "Yes"
End Select
wsWorkSheet.Activate
.Cells(xRow, i + 1) = strTempFieldValue
Else
wsWorkSheet.Activate
.Cells(xRow, i + 1) = strFieldValue
End If
'Debug.Print strDisplayText

Next i
End With

oDoc.Close savechanges:=wdDoNotSaveChanges
Set oDoc = Nothing

'Get the next doc
strDocFiles = Dir
Loop

oWord.Quit
'Make sure you release object references.
Set oWord = Nothing
Set oDoc = Nothing

'Set the header row to Bold font only
wsWorkSheet.Activate
wsWorkSheet.Cells.Select
Selection.Font.Bold = False
Rows(intHeaderRow & ":" & intHeaderRow).Select
Selection.Font.Bold = True

'Select the whole Excel sheet and expand all the columns
wsWorkSheet.Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
ActiveWorkbook.Save
Exit Sub

Err_Handler:
Select Case Err
Case -2147022986, 429
Set oWord = CreateObject("Word.Application")
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " & "No data imported.", vbOKOnly, "Document Not Found"
Case 5941
MsgBox Err.Description
' '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
oWord.Quit
End Select
End Sub

 

[SkipVought]

Hi,

Use Word HELP, Object Browser and/or Watch Window to understand what properties are exposed for any OBJECT. Some properties are NOT available in certain contexts.

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Chieh,

Posting great slabs of code, especially when you don't use the forum's code formatting tags and the code mostly isn't relevant to the problem at hand isn't terribly useful.

A couple of times now, you've referred to:

the title or label that's on top of each textbox field

and

the title or label that's on top or beside each textbox field

Whatever these labels are, they have nothing to do with formfields. Formfields don't have labels. Furthermore, your refernce to 'textbox field' suggests you may not even be using formfields, since there is no such thing as a textbox formfield. There are text formfields and there a textbox ActiveX controls. These are quite different, but even ActiveX controls don't have a "title or label that's on top". So, until you can provide some clarity about what you mean by 'textbox field', what these labels are and where they are in relation to the 'textbox fields' (eg text in an adjoining table cell or pargraph), I doubt much progress will be made.

Finally, I really don't see why you're so concerned about retrieving whatever these labels are. If you're in control of the form's design, you should already know what the function of each formfield is and where it's result should go in your Excel workbook. I've programmed the same kind of extraction process in the past, for a survey document that had hundreds of formfields and unprotected Sections for free text entry and not once did I need to know anything about a given formfield's 'caption' in order to place the data in the correct Excel column. All I needed to know was its index #.

Cheers
Paul Edstein
[MS MVP - Word]

 

[chieh]

Hi Paul:

I agreed, it is confusing, my apology as a newbie in VBA, I guess I didn't quite explain it very well. Here's more detail explaination and example...

Let say I have 5 word forms.

Each form has the following:

test1.doc
First Name [Chieh ]
Last Name [zh ]
Address [ 123 anywhere street]
;
;
;
test2.doc
First Name [Peter Smith ]
Last Name [King ]
Address [ 123 King Street]
;
;
;
and so on...

and after I run through my vba code in Excel, it will populate all the info from the word doc to excel sheet row by row... And I am trying to get the text label / or caption infront of each textbox i.e. "First Name", "Last Name", "Address", ..... and use it as column heading....

i.e. in my Excel sheet, after running the program the row should look like this:

Cell |A | B | C | D |E
-----------------------------------------------------------------------
Row 1 | | txtFirstName | txtLastName | txtAddress
Row 2 |Test1.doc |Chieh |Zh |123 anywhere st..
Row 3 |Test2.doc |Peter Smith |KIng |123 king street
Row 4 |Test3.doc ..... ..... ...
Row 5 |Test4.doc ..... ..... ...


at this point, the program is using oDoc.FormFields(i).Name as column heading for first row, I want the first row to look like the following.... using the caption or label instead of the txtFirstName, txt.., txt....

Row 1 | | First Name | Last Name | Address

Hopefully I have explained this more clearly.

Thank you again.

 

[SkipVought]

@chieh, Help youself out by using the resources I suggested.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chieh]

Thanks Skip,

I did use the watch window during debug mode and look through almost all word doc object, still no luck. Maybe some object or feature are simply not available.

Reards,
Chieh

 

[SkipVought]

As you can see, there is no CAPTION property for FormFields.

There is, however, a Name property. You ought to be able to use THAT, yes?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chieh]

Hi Skip:

Yes, at this point I am using the .Name property, i.e. oDoc.FormFields(i).Name

I may have to stick with this instead. Unless someone have any other methods or ideas.

Regards,
Chieh

 

[macropod]

And I am trying to get the text label / or caption infront of each textbox i.e. "First Name", "Last Name", "Address", ..... and use it as column heading....

That's rather like putting the cart before the horse.
Firstly, you don't want or need to gather this data from every file. You only need to get it once.
Secondly, especially given the first point, unless there's a very good reason why the Excel workbook won't exist before you run the macro, you should be setting up the headings - probably manually (it's hardly a big task and coding & debugging it would probably take longer than doing it).
Thirdly, as I've already said twice, your 'labels' have nothing to do with the formfields - they just happen to be located nearby. Accordingly, you're wasting your time playing with the .Name property. So far, all you've indicated is some 'labels' that appear to be on the same lines as certain formfields - none of them is 'on top' like you've said twice before.
Fourthly, before anything could be programmed - notwithstanding all of the above - we'd need to know for certain whether all of the 'labels' are on the same line, preceding the formfields, or if some are after, above or below the formfields and, if so, exactly how those labels' positions are to be identified programmatically.

Cheers
Paul Edstein
[MS MVP - Word]

 

[chieh]

Hi Paul:

Thanks for your feedback. Your suggestion of setting up the column heading in Excel first, would be the better solution. I guess I went overboat in trying to making it over genetic.

Regards
Chieh

 

[SkipVought]

If you were designing a macro to open and process various DIFFERENT forms/data into Excel, then you might want a more general approch.

But Paul's suggestion is best for a single form process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}