• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This code will take data from a recordset into a Word document. This code will create Tables and co

This code will take data from a recordset into a Word document. This code will create Tables and co

This code will take data from a recordset into a Word document. This code will create Tables and columns in the document and populate.

API Declarations


Dim gconPWSConnection As New ADODB.Connection
Dim mrstEmp As New ADODB.Recordset

'Before proceeding do the following
> set reference for Microsoft ActiveX Data Objects 2.7 Library using Project->Refences..
>I am assuming that u have SQL server installed, and have Northwind database and Employees table in it
>In Connection string, specify your server name,database name(northwind),and User name and Password

>In Form Add a push buton and on click event copy and paste the following code



Rate This code will take data from a recordset into a Word document. This code will create Tables and co

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

Set objMyWordInstance = CreateObject("Word.Application") 

Dim strConnectionString As String 
  
Dim iNumRows As Integer
Dim iNumCols As Integer
    
iNumRows = 1
With objMyWordInstance
        .Documents.Add 
        .ActiveDocument.PageSetup.LeftMargin = 70 
        .ActiveDocument.PageSetup.rightMargin = 70 
        .ActiveDocument.PageSetup.TopMargin = 30 
        .Selection.Font.Name = "Verdana"
Call .Application.ActiveDocument.Tables.Add(.Application.ActiveDocument.Range, 10, 5)
         
        .ActiveDocument.Tables(1).Cell(1, 1).Select
        .Selection.Font.Size = 8
        .Selection.Font.Bold = True
        
        .Application.ActiveDocument.Tables(1).Cell(1, 2).Select
        .Selection.Font.Size = 8
        .Selection.Font.Bold = True
        
        .Application.ActiveDocument.Tables(1).Cell(1, 3).Select
        .Selection.Font.Size = 8
        .Selection.Font.Bold = True
        
        .Application.ActiveDocument.Tables(1).Cell(1, 4).Select
        .Selection.Font.Size = 8
        .Selection.Font.Bold = True
        
        .Application.ActiveDocument.Tables(1).Cell(1, 5).Select
        .Selection.Font.Size = 8
        .Selection.Font.Bold = True

        .Application.ActiveDocument.Tables(1).Cell(1, 1) = "Employee ID"
        .Application.ActiveDocument.Tables(1).Cell(1, 2) = "Employee Name"
        .Application.ActiveDocument.Tables(1).Cell(1, 3) = "Title"
        .Application.ActiveDocument.Tables(1).Cell(1, 4) = "Address"
        .Application.ActiveDocument.Tables(1).Cell(1, 5) = "City"
        
strConnectionString = "Provider=SQLOLEDB;Data Source=MCD05;Initial Catalog=Northwind;User ID=admin;PWD=admin"

        gconPWSConnection.ConnectionString = strConnectionString
        gconPWSConnection.Open

mrstEmp.Open "SELECT employeeid,lastname,title,address,city FROM employees ORDER BY employeeid", gconPWSConnection, adOpenForwardOnly, adLockReadOnly
        
If mrstEmp.EOF = True Then
   Exit Sub
Else
Do While mrstEmp.EOF = False
iNumRows = iNumRows + 1
                
.ActiveDocument.Tables(1).Cell(iNumRows, iNumCols + 1).Select
.ActiveDocument.Tables(1).Cell(iNumRows, iNumCols + 1).WordWrap = True
.Selection.Font.Size = 8
.ActiveDocument.Tables(1).Cell(iNumRows, iNumCols + 1) = mrstEmp("employeeid")
.ActiveDocument.Tables(1).Cell(iNumRows, iNumCols + 2) = IIf(IsNull(mrstEmp("lastname")), "", mrstEmp("lastname"))

ActiveDocument.Tables(1).Cell(iNumRows, iNumCols + 3) = IIf(IsNull(mrstEmp("title")), "", mrstEmp("title"))

.ActiveDocument.Tables(1).Cell(iNumRows, iNumCols + 4) = IIf(IsNull(mrstEmp("address")), "", mrstEmp("address"))

.ActiveDocument.Tables(1).Cell(iNumRows, iNumCols + 5) = IIf(IsNull(mrstEmp("city")), "", mrstEmp("city"))
                
mrstEmp.MoveNext
Loop
End If
.ActiveDocument.SaveAs ("C:\Employees.doc")
Call MsgBox("[Employees.doc] document Saved Successfully in C:", vbInformation + vbOKOnly, "<>")
End With

mrstEmp.Close
    
objMyWordInstance.Quit
Set objMyWordInstance = Nothing
Set mrstEmp = Nothing
    
gconPWSConnection.Close
Set gconPWSConnection = Nothing