Article ID: 261999 - Last Review: April 19, 2007 - Revision: 5.1

How to transfer an ADO Recordset to a Word table with Automation

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q261999

Expand all | Collapse all

SUMMARY

This article demonstrates how to use automation to insert an ActiveX Data Objects (ADO) Recordset into a Microsoft Word document and then convert the text to a table.

Back to the top

MORE INFORMATION

Follow these steps to create the sample:

Start Microsoft Visual Basic and create a new Standard EXE project. Form1 is created by default.
Add a CommandButton control to Form1.
On the Project menu, click to select References, and then click to select one of the following:
- Click Microsoft Word 12.0 Object Library to add a reference to Microsoft Office Word 2007.
- Click Microsoft Word 11.0 Object Library to add a reference to Microsoft Office Word 2003.
- Click Microsoft Word 10.0 Object Library to add a reference to Microsoft Word 2002.
- Click Microsoft Word 9.0 Object Library to add a reference to Microsoft Word 2000.
- Click Microsoft Word 8.0 Object Library to add a reference to Microsoft Word 97.
Also from References, select Microsoft ActiveX Data Objects 2.1 Library to add a reference to ActiveX Data Objects, and then click OK.

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

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

Back to the top

REFERENCES

For more information on Office Automation, visit the Microsoft Office Development support site at:

http://support.microsoft.com/ofd (http://support.microsoft.com/ofd)

Back to the top