Integrating Excel and Access

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") ...