Populating a Word Document Using ADO

This sample project demonstrates how to use ADO 2.1 to fill a table in a Word 2000 document.

Download a copy of the project.

Making the Word ADO Project

  1. Create a DSN referring to the Northwind database. For information on creating DSNs, see these two MSDN Help topics:
  2. Open a new project in Visual Basic 6.0, and add references to the Microsoft ActiveX Data Objects 2.1 Library, and the Microsoft Word 9.0 Object Library.
  3. Add a command button to the form.
  4. Copy the following code into the form.
    Option Explicit

    Private Sub Command1_Click()
    'This procedure opens a Word 2000 document and a Northwind table,
    'creates a table in the Word doc, and fills the table with data.

        Dim wrdApp As Word.Application
        Dim wrdDoc As Word.Document
        Dim wrdTitle As Word.Range
        Dim wrdTableRange As Word.Range
        Dim wrdTable As Word.Table
        Dim cnNWind As ADODB.Connection
        Dim rsNWind As ADODB.Recordset
        Dim lngCounter As Long
        Dim strSQL As String

        'Use a DSN and a Connection object to
        'open the Products worksheet.
        Set rsNWind = New Recordset
        Set cnNWind = New Connection
        cnNWind.Open "northwind"
        strSQL = "SELECT TOP 6 UnitPrice,ProductName,UnitsInStock FROM Products;"
        rsNWind.Open strSQL, cnNWind, adOpenStatic, adLockOptimistic, adCmdText

        'Open a Word document, and create an object
        'variable referring to a worksheet.
        Set wrdApp = New Word.Application
        Set wrdDoc = wrdApp.ActiveDocument

        'Add blank paragraphs to the Word doc.
        For lngCounter = 0 To 10

        'Insert a 2nd-level heading in the Word doc.
        Set wrdTitle = wrdDoc.Paragraphs(4).Range
        wrdTitle.InsertAfter Format(Date, "long date")

        'Add a 3-column, 7-row table to the Word doc.
        Set wrdTableRange = wrdDoc.Paragraphs(7).Range
        Set wrdTable = wrdDoc.Tables.Add(Range:=wrdTableRange, numrows:=7, numcolumns:=3)

        'Insert column headings.
        With wrdTable
            .Cell(Row:=1, Column:=1).Range.InsertAfter Text:="Product"
            .Cell(Row:=1, Column:=2).Range.InsertAfter Text:="Price"
            .Cell(Row:=1, Column:=3).Range.InsertAfter Text:="Stock Available"
        End With

        'Insert field values.
        lngCounter = 1
        For lngCounter = 1 To 6
            With wrdTable
                .Cell(Row:=lngCounter + 1, Column:=1).Range.InsertAfter Text:=rsNWind!ProductName
                .Cell(Row:=lngCounter + 1, Column:=2).Range.InsertAfter Format(rsNWind!UnitPrice, "currency")
                .Cell(Row:=lngCounter + 1, Column:=3).Range.InsertAfter Text:=rsNWind!UnitsInStock
            End With
        Next lngCounter

        'Apply formatting to the table.
        wrdTable.AutoFormat Format:=wdTableFormatProfessional, applyborders:=True, applyfont:=False

        'Display Excel.
        wrdApp.Visible = True

        Set rsNWind = Nothing
        Set cnNWind = Nothing
        Set wrdTable = Nothing
        Set wrdDoc = Nothing
        Set wrdApp = Nothing

    End Sub
  5. Save the project, then run it.
Last changed 10/4/00.