Results 1 to 7 of 7
  1. #1
    birth19 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    7

    Import Word Forms into Access Table

    I have 100+ word documents filled out by our engineers.

    I am looking to upload the documents into a Access Database rather then manual entry

    I have been looking about and found a guide on how to do this but I think some parts of the code need updating.



    If someone can tell me where I am going wrong with this or suggest another code that would be a great help.

    The forms are kept in P:\Engineer Forms\ and my database is called TI Information.laccdb and also kept in the P:\ drive.


    Code:
      Sub GetWordData()
      Dim appWord As Word.Application
      Dim doc As Word.Document
      Dim cnn As New ADODB.Connection
      Dim rst As New ADODB.Recordset
      Dim strDocName As String
      Dim blnQuitWord As Boolean
       
      On Error GoTo ErrorHandling
       
      strDocName = "P:\Engineer Forms\" & _
          InputBox("Enter the name of the Word contract " & _
          "you want to import:", "Import Contract")
       
      Set appWord = GetObject(, "Word.Application")
      Set doc = appWord.Documents.Open(strDocName)
       
      cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=J:\" & _
          "TI Information.laccdb;"
      rst.Open "TI Information", cnn, _
          adOpenKeyset, adLockOptimistic
       
      With rst
          .AddNew
          !SiteID = docx.FormFields("fldsiteid").Result
          !TIDate = docx.FormFields("flddate").Result
          !EngineerName = docx.FormFields("fldname").Result
          !Numberofengineers = docx.FormFields("fldnumber").Result
          !Timeonsite = docx.FormFields("fldtimeon").Result
          !Timeoffsite = docx.FormFields("fldtimeoff").Result
          !Floorwalktimeon = docx.FormFields("fldwalkon").Result
          !Floorwalktimeoff = docx.FormFields("fldwalkoff").Result
          !Scopeofwork = docx.FormFields("fldwork").Result
          !Totalnumberofhandsets = docx.FormFields("fldhandsets").Result
             
          .Update
          .Close
      End With
      doc.Close
      If blnQuitWord Then appWord.Quit
      cnn.Close
      MsgBox "TI form Imported!"
       
      Cleanup:
      Set rst = Nothing
      Set cnn = Nothing
      Set doc = Nothing
      Set appWord = Nothing
      Exit Sub
      ErrorHandling:
      Select Case Err
      Case -2147022986, 429
          Set appWord = CreateObject("Word.Application")
          blnQuitWord = True
          Resume Next
      Case 5121, 5174
          MsgBox "You must select a valid Word document. " _
              & "No data imported.", vbOKOnly, _
              "Document Not Found"
      Case 5941
          MsgBox "The document you selected does not " _
              & "contain the required form fields. " _
              & "No data imported.", vbOKOnly, _
              "Fields Not Found"
      Case Else
          MsgBox Err & ": " & Err.Description
      End Select
      GoTo Cleanup
      End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Why do you think the code needs updating? Have you tried it and encountered error message, wrong results, nothing happens? Refer to the link at bottom of my post for guidelines on debugging techniques.

    That code looks like it gets data from Word document, not actually saving the document as an attachment in table. Is that what you want?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Aug 2012
    Posts
    13

    Same code problem

    Good day,
    I'm totally new to Access and VB. I work for a company in Japan and we are hiring many forigners. Like the original poster, I have several Word docs w/ field codes that I want to import into a database. I ran across the same coding that birth19 found (Import Data Directly from Word Forms to Access Tables, by Sean Kavanagh). It was written for Access & Word 2000.
    I too am having trouble. It opens the document, but errors w/ "-2147217900". The box mentions something about using 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'. I do not know where to begin.Click image for larger version. 

Name:	Script Error.JPG 
Views:	14 
Size:	11.6 KB 
ID:	8667

    I'm using Office 2003 Professional (albiet the Japanese version at work [English at home])

    The only code differences are the file locations, database & table names, and field codes; the bulk of the code is identical.

    What's wrong? Which "command" do I use and where within the code?

    Thank you in advance,

    M. McAllister

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Possible issues with the code posted by birth19:

    1. Instead of the cnn.Open, try:
    Set cnn = CurrentProject.Connection

    2. You declare variable doc but then use docx in the AddNew code.

    I am trying to run this procedure but I keep error handler message 'The document you selected does not contain the required form fields. No data imported.' The actual VBA error is 'requested member of the collection does not exist' Maybe I have not built the Word form document properly but I don't know what needs fixing. I used the ContentControls then LegacyForms controls. I used following code in a module behind the Word document to test if FormFields are in the document and it confirms they are (after using the LegacyForms controls). However, the import procedure still has the same error. I am at a loss how to fix.

    Sub FFNames()
    For Each FF In ActiveDocument.FormFields
    Debug.Print FF.Name
    Stop
    Next
    End Sub

    Also, make sure you have selected VBA reference: Microsoft Word x.0 Object Library.

    Mark, have you step debugged (refer to link at bottom of my post) - what line triggers the error?
    Last edited by June7; 08-02-2012 at 02:16 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Aug 2012
    Posts
    13
    Sorry for the late reply, as I was on an extended weekend. As I mentioned in my first post, I am running MS Access 2003, and the code was written for MS Access 2000, follow the link to the article: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
    I don’t know if there were significant changes between the two, as I said, I’m a total newbie to Access and Visual Basic.
    The article mentions adding the Word x.0 library, which was done. I did notice birth19 was missing ‘tbl’ in front of the actual table name.

    In my first post, I also mentioned I have the English version at home, the same 2003. This makes it easy to understand, since my Japanese is not strong. I step debugged the script (after scrutinizing it against the original code in the article, I found a syntax error or two). It dies at the highlighted section below:

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=(Access Database Location)" & _
    "(Access Database Name).mdb;"
    rst.Open "tbl(Data Table Name)", cnn, _
    adOpenKeyset, adLockOptimistic


    It opens the document, and apparently the table. When I was fixing the aforementioned syntax errors I got the following dialog box.
    Click image for larger version. 

Name:	Error msg 2.JPG 
Views:	11 
Size:	12.8 KB 
ID:	8703


    All subsequent runs got the same error as the first, in the original post.
    Click image for larger version. 

Name:	Error msg.JPG 
Views:	10 
Size:	11.6 KB 
ID:	8704

    I possibly closed and reopened it in between. It seems I need to INSERT a new record in the table and populate it with the information in the form.
    [Now, the table does not have a Primary Key, since it is the only table in the database.]

    I have a question: Are spaces allowed in table names? Mine has one.

    I have another question: Do I need an SQL server to run this? I don’t, at work or home.

    As for your suggestion about setting the cnn and not Opening it, the step debug, suggests it wouldn’t help any, since the problem seems to lie beyond that point. Besides, this is not a project (need an SQL server for that {?}).

    How can I rewrite the rst statement to utilize the INSERT, or whichever I actually need to get the job done?

    Thanks,

    MNM

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Objects (tables, queries, forms, reports), fields, and controls can be named whatever you want. Prefixes such as 'tbl' is a convention followed by many developers but is not required. If spaces or special characters are used in names or reserved words are used as names, must be enclosed in [].

    'Project' is often used synonymously to simply mean an Access database file (or multiple files if a split design).

    I don't know why you are encountering those errors. If you want to provide your 'project' for analysis, follow instructions at bottom of my post.

    As I said, I can almost get the procedure to work, just can't get it to recognize the form fields in the Word document I created.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Join Date
    Aug 2012
    Posts
    13
    June7,
    I got it working. This is a link to a practice set: https://www.box.com/s/e91659ae648720337dc2.
    The Word 'bookmarks' in the field can only be 20 characters long and no spaces. This also needs to match what is in the parentheses in the code (bold underlined text):
    !GivenNames = doc.FormFields("fldGivenNames").Result
    This might be the possible problem.

    Thanks for your assistance, it really helped.

    MNM

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Word document using VBA
    By degras in forum Import/Export Data
    Replies: 4
    Last Post: 04-12-2011, 02:40 AM
  2. Import a list of names from Word to access
    By WickedGoodOutdoors in forum Import/Export Data
    Replies: 3
    Last Post: 01-04-2011, 05:25 PM
  3. How to import word and PDF files into Access
    By asaini in forum Import/Export Data
    Replies: 1
    Last Post: 09-03-2009, 11:11 AM
  4. How do I get info from Access table to Word doc.
    By slim_jim_56 in forum Import/Export Data
    Replies: 0
    Last Post: 10-04-2007, 09:56 PM
  5. Importing a table created in word to Access
    By anthony_f_justin in forum Access
    Replies: 4
    Last Post: 01-04-2006, 09:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums