Microsoft KB Archive/261999

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

Article ID: 261999

Article Last Modified on 4/19/2007

-

APPLIES TO


 * Microsoft Office Word 2007
 * Microsoft Office Word 2003
 * Microsoft Word 2002 Standard Edition
 * Microsoft Word 2000 Standard Edition
 * Microsoft Word 97 Standard Edition

-



This article was previously published under Q261999



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.



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.</li> Click Microsoft Word 8.0 Object Library to add a reference to Microsoft Word 97.</li></ul> </li> Also from References, select Microsoft ActiveX Data Objects 2.1 Library to add a reference to ActiveX Data Objects, and then click OK.</li>  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 = &quot;C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb&quot;

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(&quot;Word.Application&quot;) ' 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(&quot;ADODB.Connection&quot;) ' Open our connect oConn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & _ sFileName & &quot;;Persist Security Info=False&quot; ' Execute a SQL statement to retrieve the information Set oRS = oConn.Execute( _   &quot;SELECT CustomerID, CompanyName, ContactName FROM Customers&quot;) ' Use GetString to return the recordset as a string sTemp = oRS.GetString(adClipString, -1, vbTab) ' Insert a heading on the string sTemp = &quot;Customer ID&quot; & vbTab & &quot;Company Name&quot; & _ vbTab & &quot;Contact Name&quot; & 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 </li> Press the F5 key and click on the CommandButton to run the sample.</li></ol>

<div class="references_section">