ACC95: How to Import Microsoft Word Tables Using OLE Automation

ID: Q141700


The information in this article applies to:
  • Microsoft Access 7.0
  • Microsoft Word for Windows 95, version 7.0


SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how you can import a table from a Microsoft Word for Windows document using OLE automation. You can use this technique to import data source documents created with the Mail Merge Helper in Microsoft Word.

This article assumes that the document contains only a table, which begins at the beginning of the document, and whose first record contains field names. It also assumes that the Word document is not open at the time in which the code is run to import the table. Finally, it assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access for Windows 95" manual.


MORE INFORMATION

The following function prompts you for the name of the Word data document to import and the number of columns and rows in the table. If you do not know the total number of rows in the document, you can overestimate the number. Later, you can delete any blank rows after the data is imported. Because the first record of a Word Mail Merge data document contains field names, it is important that these field names are legitimate field names in a Microsoft Access table.

  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.



REFERENCES

For more information about OLE Automation, search for OLE Automation and then OLE Automation using the Microsoft Access Help Index.

For more information about OLE Automation, search on the phrase OLE Automation and then view "Sharing information between office products" using the Answer Wizard from the Microsoft Word for Windows 95 Help menu.

Additional query words:

Keywords : kbole IntpOlea
Version : WINDOWS:7.0
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: November 11, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.