9.2. Getting Information from Microsoft Word

If you need information from Word, there are many fill-in forms available for purchase, or you may have created one yourself. Say you have emailed a form to everyone in your organization, and they filled it in and emailed it back. Now you have a directory full of Microsoft Word documents, and you want to get that information into a database.

If you created the document, the form fields should have the same name as the database fields, but if you didn't, there is a possibility that you might not even know what the fields are called. When a document is protected, you can't view the properties of the fields by right-clicking on them, but there is an easy method to determine what the fields are called. Go into the protected document and type what you want the name to be into each field. Then go to File Save As in Word and give it a new name.

Next, go into Microsoft Access and create a new database and a new table called tbl_WordFields. In the table, you need two fields called FormFieldName and DatabaseFieldName set for a type of text. Set a reference to Microsoft Word by going to Tools References in the Design View of the module in Access, and refer to Example 9-3.

Example 9-3. Code to document form fields in a protected Word document

 Public Sub ProcessWordFields(  ) Dim wapp As Word.Application Dim wdoc As Word.Document Dim wfld As Word.FormField Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb Set rs = db.OpenRecordset("tbl_WordFields") Set wapp = New Word.Application wapp.Visible = True Set wdoc = wapp.Documents.Open _   ("C:\BookInformation\Chapter9\WordFilledIn.doc") For Each wfld In wdoc.FormFields   rs.AddNew     rs.Fields("FormFieldName").Value = wfld.Name     rs.Fields("DatabaseFieldName").Value = wfld.Result   rs.Update Next wfld Set wfld = Nothing wdoc.Close Set wdoc = Nothing wapp.Quit Set wapp = Nothing rs.Close Set rs = Nothing Set db = Nothing End Sub

This code is actually a very simple iteration through the form fields in the Word document; it stores the name of the form field and the result (remember the value of a form field is stored in a property called result) in a database table. Depending on the Word document, you may have had to put numeric values in some of the fields because they did not have a type of text. In that case, you might have to edit this table by replacing the numeric value with the name of the database field. To make it easy on yourself, when entering the data into the Word document, put a value of 1 in the first numeric field, 2 in the second, etc., writing down what each number is as you go. Once you have entered the data into the Word document and run the procedure, you have a table in Access that includes all of the form fields. Once you have edited any of the numeric values in the Access table, use the table to process the Word documents that are filled in.

First, create a collection, fldcoll, which includes the database field name as the value and the form field name as the key. Then create a second collection that uses the form field name as the value. This serves as a collection of all of the keys. Next, open each Word document in a directory, pull in the information, and put it in a table. Assume you have a table in your database called tbl_ProcessedInformation that has all of the fields documented in the table called tbl_WordFields. Look at Example 9-4, keeping in mind that your filenames and paths may be different on your system.

Example 9-4. Example to process all Word documents in a folder

 Public Sub GetWordInfo(  ) Dim flname As String Dim var As Variant Dim wapp As Word.Application Dim wdoc As Word.Document Dim db As DAO.Database Dim rs As DAO.Recordset Dim fldcoll As Collection Dim dbcoll As Collection Set fldcoll = New Collection Set dbcoll = New Collection Set db = CurrentDb Set rs = db.OpenRecordset("tbl_WordFields") rs.MoveFirst While Not rs.EOF   dbcoll.Add rs.Fields("DatabaseFieldName").Value, _       rs.Fields("FormFieldName").Value   fldcoll.Add rs.Fields("FormFieldName").Value   rs.MoveNext Wend rs.Close Set rs = db.OpenRecordset("tbl_ProcessedInformation") Set wapp = New Word.Application wapp.Visible = True flname = Dir("C:\BookInformation\Chapter9\WordProcess\*.doc") While flname <> ""   Set wdoc = wapp.Documents.Open _     ("C:\BookInformation\Chapter9\WordProcess\" & flname)   rs.AddNew   For Each var In fldcoll     rs.Fields(dbcoll.Item(var)).Value = _       wdoc.FormFields(var).Result   Next var   rs.Update   wdoc.Close   flname = Dir Wend wapp.Quit Set wdoc = Nothing Set wapp = Nothing Set dbcoll = Nothing Set fldcoll = Nothing rs.Close Set rs = Nothing Set db = Nothing End Sub

Notice that I used the variable rs for both of the recordsets because I did not have to have both open at the same time. To go through all of the Word documents in a directory, I use the Dir function along with a While...Wend loop. Once the document is open, the code iterates through all of the values in the fldcoll collection, the collection of keys where the key value is the name of the form field in Word. So, for each value in that collection, the database field is the dbcoll collection's value where the key is the form field name from Word. The value of the database field is then set to the form field's result.

In a Word document where you might have five or six fields, it might make sense to use the information from your table to write code for each field name. However, if you are using a form with twenty or even hundreds of fields, this code saves you a lot of time, and once it is written, you can reuse it many times.

I should also mention that you can go to File Save As in Word and then go to the Save Options dialog from the Tools drop-down menu in the Save As dialog. From there, you can check a box to save data only for forms. If you do that and save the document as text only, you could then import the text files into the database. I dont recommend it because it adds a lot of steps, but it is another option to get only the data from a form.

All of the examples from Word are generic enough that with minor tweaking they can be adapted for many purposes. When you have control over the Word document and the database, you can set it up to make it easy to process. When you don't have control, you can use the examples presented to document and process the Word document's fields.