HandsOn Copying Records to a Word Document Example

1. Switch to the Visual Basic Editor window and insert a new module.

2. Choose Tools | References in the Visual Basic Editor window, scroll down to locate the Microsoft Word Object Library, click the check box next to it, then click OK to exit.

3. In the Code window, enter the SendToWord procedure as shown below.

Option Compare Database Option Explicit

' be sure to select Microsoft Word Object Library ' in the References dialog box

Public myWord As Word.Application

Sub SendToWord()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim doc As Word.Document Dim strSQL As String Dim varRst As Variant Dim f As Variant Dim strHead As String

Creating and Manipulating Databases with ADO

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

strSQL = "SELECT Shipperld as Id,"

strSQL = strSQL & "CompanyName as [Company Name],"

strSQL = strSQL & "Phone FROM Shippers"

conn.Open rst.Open strSQL, conn, adOpenForwardOnly, _ adLockReadOnly, adCmdText

' retrieve data and table headings into variables If Not rst.EOF Then varRst = rst.GetString(adClipString, , vbTab, vbCrLf) For Each f In rst.Fields strHead = strHead & f.Name & vbTab Next End If

' notice that 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 myWord.Visible = True

' paste contents of variables into ' Word document doc.Paragraphs(1).Range.Text = strHead & vbCrLf doc.Paragraphs(2).Range.Text = varRst

Set myWord = Nothing End Sub

^Document! - Microsoft Word

mnlxl

File

Edit View Insart Format TqdIs Table Window Help

X

Ji

J UW 1 - - IT

759s ^::

Times New Roman 12 » B I U

gj* != ;= ®

1

m:

. i ... i ... i ... 2 ... i ... 3 .

..!,.. 4 ...!,,. 5 , .

»

2 3

CompanyName Phone Speedy Express (503) 55 5-9S31 United Package (503) 555-3199 Federal Shipping (503) 5S5-9S3I

5

T]a J

3 if! Jt_|_|

Jj

Page 1

Sec 1 1/1 At 1.5"

Ln 4 Col 19 B

Figure 14-2: After running the procedure in Hands-On 14-6, the data from the Shippers table is placed in a Word document.

Figure 14-2: After running the procedure in Hands-On 14-6, the data from the Shippers table is placed in a Word document.

Part II

0 0

Post a comment