HandsOn Copying Records to a Word Document Example

1. In the same module where you entered the procedure in Hands-On 14-6, enter the SendToWord2 procedure as shown below.

Sub SendToWord2()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset

Dim doc As Word.Document Dim WordTbl As Word.Table Dim strSQL As String Dim f As Variant Dim numRows As Integer Dim numFields As Integer Dim r As Integer Dim c As Integer

Set conn = New ADODB.Connection Set rst = New ADODB.Recordset conn.Provider="Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

strSQL = "SELECT * From Shippers"

conn.Open rst.Open strSQL, conn, adOpenStatic, _ adLockReadOnly, adCmdText rst.MoveLast rst.MoveFirst numRows = rst.RecordCount numFields = rst.Fields.Count

' notice that the Word application is declared ' at the top of the module Set myWord = New Word.Application

' create a new Word document Set doc = myWord.Documents.Add

' insert table

Set WordTbl = doc.Tables.Add _

(doc.Range, numRows + 1, numFields)

If numRows > 0 Then

' Create the column headings in table cells For Each f In rst.Fields With WordTbl

' row counter ' column counter

Creating and Manipulating Databases with ADO

Do While Not rst.EOF For c = 1 To numFields

WordTbl.Cell(r, c).Range.Text = rst.Fields(c - 1).Value Next c r = r + 1 rst.MoveNext Loop myWord.Visible = True rst.Close Set rst = Nothing Set myWord = Nothing Set conn = Nothing End Sub

0 0

Post a comment