Importing MS Word Tables into MS Access Tables using VBA

[Brilstern]

Many times we find ourselves moving from one format to another in the office space and sometimes moving data from one format to another can be a real pain in the butt. Automating these processes is always handy and let’s be honest. We all love it when things are just… easier. The key many times is to understand both formats and how they can work together to bring your data from one medium to another.

Recently I found myself in a dilemma where my Report in MS Word was just not enough and I needed to aggregate a lot of the data from many reports… Well word is good for displaying data, but not so much for storing and managing it. So my solution: move it to MS Access. Then I started trying to figure out how to do that, very little luck in finding good documentation on this. So after not really finding a satisfactory answer… I thought I would share what I am using to import information from Word tables to Access.

Required References for this tutorial:

Microsoft Access Object Library
Microsoft Office Object Library
Microsoft Word Object Library

The first issue is to understand how MS Word labels and identifies tables. MS word will auto identify any table and label according to its order on the document. Therefore the tables are listed as Tables(1) - Tables(i), “i” being the total number of tables you have. To reference these tables from MS Access a few steps need to be taken.

First we must identify and access our word document:

Dim appWord As Word.Application [I]'declare MS Word application[/I]
Dim doc As Word.Document [I]'declare an instance of a document in MS Word[/I]
Dim strDoc as String [I]'we will use this to pass a document name Word to later[/I]

'This can also be coded as 
Dim appword As Word.Application, doc As Word.Document, strDoc as String

Next we must open MS Word and declare our doc name:

strDoc = CurrentProject.Path & "\file.docx"  [I]'declare document name[/I]
Set appWord = CreateObject("Word.Application") [I]'open MS Word[/I]
Set doc = appWord.Documents.Open(strDoc) [I]'open the document that is declared in strDoc[/I]

Now we can reference tables on our word document with the following code:

doc.Tables(1).Cell(1, 2).Range.Text [I]'this points us to table 1, row 1, column 2[/I]

You will notice in the code above we don’t use row and column but Cell(x,y). This is much easier to visualize as well as coding.

A few closing statements are needed to make sure we clear up our code:

doc.Close: Set doc = Nothing [I]'this closes our word document and clears up the document selection [/I]
appWord.Quit: Set appWord = Nothing [I]'this closes our instance of MS Word[/I]

Now let’s see this in action for a simple personnel table.

This is our word table:

People (Table 1)
========================
[U]| First Name | Last Name | SSN | Phone Number | Gender |[/U]
| Data       | Data      | Data| Data         | Data   |
| Data       | Data      | Data| Data         | Data   |
| Data       | Data      | Data| Data         | Data   |
| Data       | Data      | Data| Data         | Data   |

This is our database table:

tblPeople
========================
[B][U]Field Name    Data Type[/U][/B]
FName         Text
LName         Text
SSN           Text [B](PK)[/B]
PhoneNumber   Text
Gender        Text

And Finally, The full code to import the People table from People.docx to our database table tblPeople.

Private Sub cmdImport_Click()
Dim appWord As Word.Application, doc As Word.Document
Dim dbs As DAO.Database, rst As DAO.Recordset, strDoc As String

    Set appWord = CreateObject("Word.Application") [I]'establish an instance of word[/I]
    strDoc = CurrentProject.Path & "\People.docx"  [I]'set string to document path and file[/I]
    Set doc = appWord.Documents.Open(strDoc) [I]'establish the document[/I]

    Set dbs = CurrentDb [I]'establish the database to use (this is our current Database)[/I]
    Set rst = dbs.OpenRecordset("tblPeople") [I]'establish the recordset[/I]

    With doc.Tables(1) [I]'target table 1 in People.docx[/I]
    
        For i = 2 To .Rows.Count [I]'cycle through rows in Tables(1) [we skip the first row because the table has headers][/I]

            With rst
                .AddNew [I]'creating a new record[/I]
                    ![FName] = doc.Tables(1).Cell(i, 1).Range.Text
                    ![LName] = doc.Tables(1).Cell(i, 2).Range.Text
                    ![SSN] = doc.Tables(1).Cell(i, 3).Range.Text
                    ![PhoneNumber] = doc.Tables(1).Cell(i, 5).Range.Text
                    ![Gender] = doc.Tables(1).Cell(i, 6).Range.Text
                .Update [I]'update the whole record[/I]
            End With
    
        Next [I]'go to next row in Tables(1)[/I]

    End With

    rst.Close: Set rst = Nothing [I]'close and clear recordset[/I]
    db.Close: Set rst = Nothing [I]'close and clear database[/I]
    doc.Close: Set doc = Nothing [I]'close and clear document[/I]
    appWord.Quit: Set appWord = Nothing [I]'close and clear MS Word[/I]

End Sub

This is can modified and expanded in many ways, but in its simplest form that’s all there is too it.

NOTE: Experts! Please let me know what I can do to make this small article any better. (It is my first one)

[twinnyfo]

Stevan,

Thanks for the great primer on working with MS Word Tables using VBA. Hopefully, readers will also understand that the reverse is also true, that one can modify Tables in MS Word using these principles. I do the same when working with some PowerPoint charts and graphs.

You also raise an important point, that the average office employee doesn't fully understand, that just because a document has information in it, it doesn't mean that information is useable. A Word Document is not an Access Database--but it also has a completely different purpose.

There is an office here at work that often refers to it "database"--which is really about 50 different MS Excel spreadsheets all linked together on a shared network location. I have been trying to convince them:

1. This is not a database, no matter how you define or use it
2. This structure is just waiting to crash--in a very bad way
3. They really need to convert it into a usable, database format (I'll even help them do it)

I will feel really bad for them when their data is destroyed....

Again, thanks for the work and willingness to share!

[Brilstern]

I am actually working on perfecting the reverse right now for my current project. I will probably write a sister article explaining how to do so when I have a better grasp around it.

[zmbd]

Actually, the reverse is much the same as you've already accomplished. I often do this from the database to a word document as our corporate people don't see, to want to do things the proper way :)

[Brilstern]

Webucator has teamed with me to make this article into a video! Please check it out and and subscribe to learn more about the processes they have helped bring to visual media.

Check out the awesome video here!!
Check out Webucators VBA training.
Check out Webucator.com