by karada (6 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 6th February 2003
Date Added: Mon 8th February 2021
Rating: (1 Votes)
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
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
No comments have been posted about This code will take data from a recordset into a Word document. This code will create Tables and co. Why not be the first to post a comment about This code will take data from a recordset into a Word document. This code will create Tables and co.