Home

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
        wrdApp.Documents.Add
        Set wrdDoc = wrdApp.ActiveDocument

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

        '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
            rsNWind.MoveNext
        Next lngCounter

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

        'Display Excel.
        wrdApp.Visible = True

        'Cleanup
        rsNWind.Close
        Set rsNWind = Nothing
        cnNWind.Close
        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.