Copying Records to a Word Document (Example 1) : Access to Word « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. Access to Word

Copying Records to a Word Document (Example 1)

 
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 myRecordset As ADODB.Recordset
   Dim doc As Word.Document
   Dim strSQL As String
   Dim varRst As Variant
   Dim f As Variant
   Dim strHead As String
   Set conn = New ADODB.Connection
   Set myRecordset = New ADODB.Recordset

   conn.Provider = "Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"

   strSQL = "SELECT ShipperId as Id,CompanyName as [Company Name],Phone FROM Shippers"

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

   If Not myRecordset.EOF Then
      varRst = myRecordset.GetString(adClipString, , vbTab, vbCrLf)
      For Each f In myRecordset.Fields
         strHead = strHead & f.Name & vbTab
      Next
   End If

   Set myWord = New Word.Application
   Set doc = myWord.Documents.Add
   myWord.Visible = True
   doc.Paragraphs(1).Range.Text = strHead & vbCrLf
   doc.Paragraphs(2).Range.Text = varRst

   Set myWord = Nothing
End Sub

 

Related examples in the same category