Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word file appended to Access database table (Word 2000/Access 2000)

    Can someone please send me sample VBA code to save a user-completed Word 2000 form (created using form fields) as a "comma delimited text file" and then open an Access 2000 database table and append the comma delimited text file as a new record in the Access table. For instance, the form has three fields--LastName, Degree, University--and the Access table has three columns with same names--LastName, Degree, University. Approximately 150 users will complete the Word form and return it to me; I then need to get the data into an Access database. Any suggestions anyone can offer me will be greatly appreciated. Thanks...Mary

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

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    You don't need the intermediate step of saving to a text file; you can transfer the data from Word directly into an Access table. This can be done with code that runs in Word or in Access. I have attached code to do this in Word. It uses DAO to open a recordset, so you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    The code assumes that you have stored the documents in the same folder (it should contain no other Word documents). It will scan all documents (.doc) in that folder and add a record to a table in an Access database for each document. The start of the procedure contains a series of constants that must be modified to match your situation.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Many thanks, Hans; I can hardly wait to try your code! This will be absolutely wonderful if I can get it to work for me. I'll let you know as soon as I've tried it. Again, many, many thanks for the quick response. Until next time....Mary

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Hello, Hans...I was so excited to try the code that I quickly created a test database, three form documents with the three sample fields, and a new folder to store the form documents, using all same names as in sample code. However, when I try to run the code, I keep getting an "Automation Error" message, and thus cannot step into the code to see where the problem might lie. I pasted the procedure in my Word's "Normal, New Macros" module and referenced the MS DAO 3.6 library as suggested, as well as the Microsoft Access library. Do you have any idea what I might be doing wrong or what else I need to do to test the code before I make modifications to it for my real database and real form documents? I certainly would appreciate any help you can give me. Thanks...Mary

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

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    You don't need a reference to the Access library, since Access isn't opened in the code I posted.

    To find out what's wrong, temporarily comment out the On Error GoTo ErrHandler line near the beginning. When the error occurs, you'll be given a choice of End, Debug and Help. Click Debug to see which line of code causes the error. You can hover the mouse over variables to inspect their value. If you want to halt execution, use the toolbar or the Run menu.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Mary,

    I have a demo that shows how to soemthing similar. There is a Word Form that you fill in and save. Then you open a Form in the Access DB which opens the Word form and "imports" the form fields. It also allows you to validate the input before writing it out to the tables.

    Unfortunately it's too large (284KB) to post in the Lounge, but you can grab it from my website
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Thank you, Hans. I did as you suggested and the code stops on the line "Dim doc as document" at the beginning of the procedure--I get the "Compile error, Automation Error" message and can't get further; I cannot debug to step over it. I then realized that I was home and had put the code in "Word XP", which I have running on my home computer, rather than Word 2000 (which is what is in use at my office), so then I tried the code in Word 97, which I also have on my computer at home, and recreated the forms in Word 97, and also changed the DAO reference to the 3.1 earlier version(?). Still no luck. Can you help me? Thanks...Mary

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Thank you, Bryan...Yes, I am very interested in seeing the code you mentioned in responding to my post. When I clicked on your hyperlink, though, I couldn't get the file. I also checked out your website but could not get the code file from there either. Is there something else I need to do? Many thanks...Mary

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

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Probably, the VBA interpreter is confused since Document is an object both in Word and in DAO. In this case, it's a Word document, so try changing the offending line to

    Dim doc As Word.Document

    to remove the ambiguity. If you have problems later on with Documents.Open, prefix it with Word. too:

    Set doc = Word.Documents.Open ...

    You should have a reference to DAO 3.6 in Word 2000/Word 2002, and to DAO 3.5 or 3.51 in Word 97.

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Mary,

    The link is a direct link to a zip file. Once you download the zip, then just unzip the two files. One is the Word Document and one is the Access db.

    You shouldn't need to do anything special.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  11. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Hello again, Hans...I made the clarifications you suggested, and now when I try to run the code, it reports same "Compile error: Automation error" message and stops at the following line and highlights "DBEngine" -- "Set dbs = DBEngine.OpenDatabase(Name:=cstrDatabaseName)". Do you think I should wait until I get to the office and try again in Word 2000, or is there something else we can try? Many thanks...Mary

  12. #12
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Thank you, Bryan. I got it this time when I clicked on the hyperlink. I don't know what happened with the download dialog last time, but it didn't give me the prompts that I got this time. I've downloaded the file and am looking forward to checking out the code. Again, many thanks for the helpful information....Mary

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

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    It might be that there is a problem with the coexistence of Word 97 and 2002 on your home PC, or there is a problem with references. I tested the code on a machine with only Office 2002, and it ran without problems; moreover, there is nothing in the code that is specific to Office 97, 2000 or 2002.

    Can you take a look at Tools | References... in the Visual Basic Editor and report which references are checked? Please list them in the same order as they appear in the list.

  14. #14
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word file appended to Access database table (Word 2000/Access 2000)

    Great news, Hans! I'm at my office computer now (MS Office 2000) and when I copied your code on this machine, and recreated the test files, the code works beautifully! I'm so excited! :-) Thank you very much! When I get home, I'll send you the info you requested so you can maybe figure out why I can't run the code on my home computer. Once again, Hans, thank you so much for your expert help!...Mary

Posting Permissions

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