HOWTO: Transfer an ADO Recordset to a Word Table with Automation (261999)

MORE INFORMATION

1. Start Microsoft Visual Basic and create a new Standard EXE project. Form1 is created by default.
2. Add a CommandButton control to Form1.
3. On the Project menu, click to select References.
   
   Select Microsoft Word 11.0 Object Library to add a reference to Microsoft Office Word 2003. Select Microsoft Word 10.0 Object Library to add a reference to Microsoft Word 2002. Select Microsoft Word 9.0 Object Library to add a reference to Microsoft Word 2000, or select Microsoft Word 8.0 Object Library to add a reference to Microsoft Word 97.
4. Also from References, select Microsoft ActiveX Data Objects 2.1 Library to add a reference to ActiveX Data Objects, and then click OK.
5. Insert the following code in the Code window for Form1. Note that you might need to change the sFileName variable to point to the correct location of the Northwind database.

   Option Explicit
   Const sFileName = "C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
   
   Private Sub Command1_Click()
     ' Declare our variables
     Dim oWord As Word.Application
     Dim oDoc As Word.Document
     Dim oRange As Word.Range
     Dim oConn As ADODB.Connection
     Dim oRS As ADODB.Recordset
     Dim sTemp As String
     
     ' Create an instance of Word
     Set oWord = CreateObject("Word.Application")
     ' Show Word to the user
     oWord.Visible = True
   
     ' Add a new, blank document
     Set oDoc = oWord.Documents.Add
     ' Get the current document's range object
     Set oRange = oDoc.Range
     
     ' Create a new ADO connection
     Set oConn = CreateObject("ADODB.Connection")
     ' Open our connect
     oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        sFileName & ";Persist Security Info=False"
     ' Execute a SQL statement to retrieve the information
     Set oRS = oConn.Execute( _
       "SELECT CustomerID, CompanyName, ContactName FROM Customers")
     ' Use GetString to return the recordset as a string
     sTemp = oRS.GetString(adClipString, -1, vbTab)
     
     ' Insert a heading on the string
     sTemp = "Customer ID" & vbTab & "Company Name" & _
       vbTab & "Contact Name" & vbCrLf & sTemp
     ' Insert the data into the Word document
     oRange.Text = sTemp
     ' Convert the text to a table and format the table
     oRange.ConvertToTable vbTab, , , , wdTableFormatColorful2
   End Sub
   					

6. Press the F5 key and click on the CommandButton to run the sample.