Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Data From Word (A2000)

    GM All,

    I don't want to be long winded, but I feel I need to give a little info so that all understand clearly.

    In word, a document has several dropdown boxes that the user makes selections from (i.e. the answers to questions)

    I would like to know if there is a way of taking the responses from the dropdowns (in Word) and exporting them to a table in Access?

    Thanks in advance for any suggestions.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data From Word (A2000)

    You write that the document has dropdown boxes, so I assume you mean that the dropdown boxes are in the document itself, not on a userform.
    Were they created from the Forms toolbar or from the Controls Toolbox?

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data From Word (A2000)

    Hans,

    I'm not sure...I didn't create the Word doc and the ppl I spoke with here don't seem to remember how they created them. There are 10 pages to this survey. They are currently manually inputting the responses from the survey (which are many) in Access tables. I was hoping there was a method of doing this thru automation, however, I'm not that familiar with Word (automation that is).

    Thanks.

    p.s. and yes...they are in the document itself and not on a userform.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data From Word (A2000)

    Roberta,

    Is the part of the document that contains the dropdown lists protected, i.e. can the user only fill in certain fields (that most probably have a gray background), and not edit the rest of the text? Or do the dropdown lists look like in Access? See screenshot.
    Attached Images Attached Images

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data From Word (A2000)

    Hans,

    Thanks....Ok...from the forms toolbar....but I also failed to mention that the data may come several types of sources. This document has check, drop, and text boxes. Please know I'm not asking for miracles.....only to get me started or pointed in the right direction....I didn't even think it could be done.

    Thanks again.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data From Word (A2000)

    All form fields (text boxes, check boxes and combo boxes) have a Result property. For text boxes, this is the text entered by the user; for check boxes, 0 means unchecked and 1 means checked; for combo boxes, Result is the selected item. Form fields also have a property Name; this is the name of the bookmark associated with the form field. It is only useful if the person that designed the document assigned meaningful bookmark names.

    You can loop through the FormFields collection of the document to retrieve the Name and Result of each form field.

    To manipulate a Word document from Access, you must set a reference (in Tools | References...) to the Microsoft Word 9.0 Object Library. (9.0 is the version number for Word 2000; for Word 97 it is 8.0 and for Word 2002 it is 10.0.)

    The code could look like this:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub RetrieveFieldValues(strFileName As String)
    Dim objWord As New Word.Application
    Dim objDoc As Word.Document
    Dim objFormField As Word.FormField
    Dim strName As String
    Dim varResult As Variant

    On Error GoTo ErrHandler

    ' Open document
    Set objDoc = objWord.Documents.Open(strFileName)
    ' ' Loop through form fields
    For Each objFormField In objDoc.FormFields
    strName = objFormField.Name
    varResult = objFormField.Result
    ' Do something with these variables - that's up to you
    MsgBox strName & vbCrLf & varResult
    Next objFormField

    ExitHandler:
    ' Clean up
    On Error Resume Next
    Set objFormField = Nothing
    objDoc.Close SaveChanges:=wdDoNotSaveChanges
    Set objDoc = Nothing
    objWord.Quit SaveChanges:=wdDoNotSaveChanges
    Set objWord = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    Call it like this:

    RetrieveFieldValues "C:WordSurvey.doc"

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data From Word (A2000)

    Thanks Hans...I will give this a try...I appreciate your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data From Word (A2000)

    Hans,

    Below is the modified code

    Dim objWord As New Word.Application
    Dim objDoc As Word.Document
    Dim objFormField As Word.FormField
    Dim strName As String
    Dim varResult As Variant
    Dim db As DataAccessPage
    Dim rs As Recordset

    On Error GoTo ErrHandler

    ' Open document
    strFileName = "Cocuments and Settingsrnewt01My DocumentsMyWordToAccessTest1.doc"
    Set objDoc = objWord.Documents.Open(strFileName)
    ' ' Loop through form fields
    For Each objFormField In objDoc.FormFields
    strName = objFormField.Name
    varResult = objFormField.Result
    ' Do something with these variables - that's up to you
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblPHResults")

    With rs
    .AddNew
    End With

    MsgBox strName & vbCrLf & varResult
    Next objFormField

    ExitHandler:
    ' Clean up
    On Error Resume Next
    Set objFormField = Nothing
    objDoc.Close SaveChanges:=wdDoNotSaveChanges
    Set objDoc = Nothing
    objWord.Quit SaveChanges:=wdDoNotSaveChanges
    Set objWord = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    I get a "Type Mismatch" error w/o any line highlighted. I don't know where to debug. It compiled fine. The references are set. Not sure where I messed up.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data From Word (A2000)

    Roberta,

    For debugging purposes, comment out the line

    On Error GoTo ErrHandler

    temporarily (insert an apostrophe ' before it). The offending line will then be highlighted - post the result. Don't forget to uncomment the line after you've ironed out all the wrinkles - the 'production' version should have error handling.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Data From Word (A2000)

    Hans,

    I fixed part of my problem...it was with the Dim db and rs ....

    I get values returned now but through a msgbox....I want them to be placed in a table...I thought I was doing that ...below are my changes.

    Dim objWord As New Word.Application
    Dim objDoc As Word.Document
    Dim objFormField As Word.FormField
    Dim strName As String
    Dim varResult As Variant
    Dim db As DAO.Database, rs As DAO.Recordset

    On Error GoTo ErrHandler

    ' Open document
    Set db = CurrentDb
    strFileName = "Cocuments and Settingsrnewt01My DocumentsMyWordToAccessTest1.doc"
    Set objDoc = objWord.Documents.Open(strFileName)
    ' ' Loop through form fields
    For Each objFormField In objDoc.FormFields
    strName = objFormField.Name
    varResult = objFormField.Result
    ' Do something with these variables - that's up to you

    Set rs = db.OpenRecordset("tblPHResults")

    With rs
    .AddNew
    End With

    MsgBox strName & vbCrLf & varResult
    Next objFormField

    ExitHandler:
    ' Clean up
    On Error Resume Next
    Set objFormField = Nothing
    objDoc.Close SaveChanges:=wdDoNotSaveChanges
    Set objDoc = Nothing
    objWord.Quit SaveChanges:=wdDoNotSaveChanges
    Set objWord = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    Am I not putting in enough code for the ".AddNew"?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Data From Word (A2000)

    At present, nothing will happen.

    In the first place, you shouldn't open the recordset again and again within the loop; you should open it once before the loop, and close it after the loop.
    In the second place, you shouldn't keep on adding new records again and again - I think.
    In the third place, just .AddNew does nothing unless you then set some fields, and update the record.
    I have assumed that the form fields in the document correspond to fields in the table and that the bookmark name is the name of the corresponding field in the table. If that is not the case, you should try to explain what you want to do with the form fields.

    <img src=/w3timages/blueline.gif width=33% height=2>

    ' Open recordset
    Set rs = db.OpenRecordset("tblPHResults", dbOpenDynaset)
    rs.AddNew

    ' Loop through form fields
    For Each objFormField In objDoc.FormFields
    strName = objFormField.Name
    varResult = objFormField.Result
    ' This is highly speculative - Isee above
    rs.Fields(strName) = varResult
    Next objFormField

    rs.Update

    <img src=/w3timages/blueline.gif width=33% height=2>

    In the ExitHandler part, add code to close the recordset and set the database variables to Nothing:

    rs.Close
    Set rs = Nothing
    Set db = Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •