Import Data From Word (A2003 SP2)

Topic

New Reply

Looking for some suggestions.

Personnel have informed me that they are going to be receiving several hundred forms filled out and saved as Word documents and want to know if there is a way to import this data to Access such that the data does not have to be manually re-input. Each file has a unique name and the data fields in the Word Docs are the same. The data appears to be input into a custom form rather then a table. I was reviewing techniques on mail merge as well as automation to get the data and figured I would see if anyone has any suggestions on how to do this. I would prefer not to change the word docs to .txt or other formats so that may force automation. Any suggestions to point me on the best way to do this. Thanks.

Reply | Quote

Replies

Are the documents protected for forms, with the data entered in form fields?

Reply | Quote

Yes, the documents are protected for forms with the data entered in form fields.

Reply | Quote

If the document was designed carefully, each formfield will have a unique bookmark name. This would make it easy to retrieve information from the form fields by name; otherwise it’ll have to be done by position.

The idea for automated data transfer is:
– Put all received documents in a folder (with no other documents).
– Open the first .doc file in the folder.
– Create a new record in the Access table.
– Read the values of the form field and put them in the appropriate fields.
– Update the record.
– Close the document without saving it.
– Move to the next document.

This can be done in a macro from Word (using DAO or ADO to open a recordset on the Access table), or from Access (using Automation to start Word, etc., and also using DAO or ADO to open a recordset).
Post back if you want more detailed help.

Reply | Quote

Thanks Hans,

I have not done much working with MS Word so this is a challenge as I am not familiar with the Word object structure. I will post back if I need help, but you pointed me in the right direction. Thanks.

Reply | Quote

Hans,

Without going into great detail, what would the object be to reference the position of a word? I know that . Words.Count returns the total words in a document but how would you reference a particular word or use a loop to search through the words? I tried looping through the words using .Words(x) where x is from 1 to .words.count but of course, got an error.

Reply | Quote

I meant that you could loop through the form fields:

Dim i As Integer
For i = 1 To objDoc.FormFields.Count
‘ do something with objDoc.FormFields(i) here
Next i

Reply | Quote

Thanks. That answered the question.

Reply | Quote

Hans,

Half of the bookmarks are text fields and half are dropdown (combo) boxes. I can retrieve the data for the text boxes without issue. However, the data on the dropdown boxes comes across as a character (similar to a perpendicular symbol). Do bookmarks that are a dropdown have a different reference to the data then text boxes?

Reply | Quote

If you want to retrieve the text of the item selected by the user, you can use

objDoc.FormFields(“Dropdown3”).Result

If you want to know the index of the item selected by the user (1, 2, 3, …), you can use

objDoc.FormFields(“Dropdown3”).DropDown.Value

Reply | Quote

Got it. Thanks.

Reply | Quote

Hans,
I would like more detailed information on how to take data that are in Word Formfields and transfer it into an Access table.

I have a Word Document that contains formfield all of which has bookmarks. I would like to transfer this information to a Access table. Preferably I would like to do this through the open Word document. However, this is not a requirement.
thanks

Reply | Quote

My previous replies in this thread tell you how to retrieve the value of form fields in a Word document.
If you want detailed instructions, please tell us in detail (not just globally) what you want to do.

Reply | Quote

PS, I see that you asked the same question in the Word forum (post 644,593). In the future, please refrain from cross posting – it can only lead to confusion and duplication.

Reply | Quote