ACC95: How to Import Microsoft Word Tables Using OLE Automation

MORE INFORMATION

1. Open a new, blank database.
2. Create a new module and type the following procedure:

          Function ImportWordDoc()
            Dim x As Variant, y As Variant, i As Variant, j As Variant
            Dim tabtext As String
            On Error GoTo Errlabel
   
            Dim db As DATABASE, t As TableDef, wordobject As Object, f As _
                    Field
            Dim r As Recordset, filename As String
   
            ' Get Word Merge Document to Import into Microsoft Access.
            filename = InputBox("Enter the Word Document With its Path,to _
            Import","Enter FileName", "C:\WINWORD")
            x = InputBox("Enter the Number of Columns", "Enter # of Columns")
            y = InputBox("Enter the Number of Rows", "Enter # of Rows")
   
            If x = "" Or y = "" Or filename = "" Then
              MsgBox "You must supply a valid filename, and the number of _
                      table columns and rows."
              Exit Function
            End If
   
            Set db = Currentdb()
            Set t = db.CreateTableDef("IMPORT WORD TABLE")
            Set wordobject = CreateObject("Word.Basic")
            wordobject.fileopen filename
            wordobject.selectcurword
   
            'Create Field Names.
            For i = 0 To x - 1
              Set f = t.CreateField(wordobject.selection(), DB_TEXT)
              t.Fields.Append f
              f.AllowZeroLength = True
              wordobject.nextcell
            Next i
   
            'Append Table to Database Table Collection.
            db.TableDefs.Append t
   
            ' Append records from Word table into Microsoft Access table,
            ' IMPORT WORD TABLE.
            Set r = db.OpenRecordset("IMPORT WORD TABLE")
            For j = 2 To y + 1
              r.AddNew
              For i = 0 To x - 1
                 tabtext = wordobject.selection()
                 ' Remove any carriage returns in the table cells.
                 While InStr(1, tabtext, Chr$(13)) <> 0
                 tabtext = Left$(tabtext, InStr(1, tabtext, Chr$(13)) - 1) & _
                 Right$(tabtext, Len(tabtext) - InStr(1, tabtext, Chr$(13)))
                 Wend
                 r.Fields(i) = tabtext
                 wordobject.nextcell
              Next i
            r.UPDATE
            Next j
            Exit Function
   
          Errlabel:
            MsgBox Error
            Exit Function
   
          End Function
   						

3. To test this function, on the View menu click Debug Window. Type the following line in the Debug window, and then press ENTER:

   ?ImportWordDoc()

4. When prompted for the document name, enter the full path and name of the Word document to import.
5. When prompted for the number of columns, enter the number of columns in the Word table.
6. When prompted for the number of rows, enter the number of rows, not including the header row (that is, the first row of the table which contains the field names).
7. Save and close the module.
8. In the Database window, click the Table tab, and then double-click the Import Word Table to view the table in Datasheet view. If you have blank rows at the end of the table, you can select and delete them.
9. Switch the Import Word Table to Design view. Note that the data type for each field is Text. You may want to modify the data types as appropriate; for example, you may want to change a date field to Date/Time.