martinmike2 said:
I would like to populate a word document containing a table but I odnt
know where to start.
Here's a code snippet that creates and formats a Word table, filling it with
data from a recordset and the form:
' Instantiate the recordset
Set rs = New ADODB.Recordset
' Open the recordset using the command object
rs.Open comCommand 'rs.Open comCommand, , adOpenKeyset, adLockOptimistic
Set objWord = CreateObject("Word.Application")
WordTemplate = Application.CurrentProject.Path & "\WorkTicket.dot"
With objWord
.Visible = True
.Documents.Add (WordTemplate)
.Caption = "Work Ticket For " & Me.cboAccountID.Column(1) & ""
' If document is protected, Unprotect it.
If .ActiveDocument.ProtectionType <> wdNoProtection Then
.ActiveDocument.Unprotect Password:=""
End If
.ActiveDocument.Bookmarks("Customer").Select
.Selection.Text = (CStr(Me.cboAccountID.Column(1)))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Me.cboAccountID.Column(2)))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Me.cboAccountID.Column(3)))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Me.cboAccountID.Column(4)))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Me.cboAccountID.Column(5)))
.ActiveDocument.Bookmarks("Phone").Select
.Selection.Text = (CStr(Me.cboAccountID.Column(8)))
.ActiveDocument.Bookmarks("DateScheduled").Select
.Selection.Text = (Format$(Me.txtDateScheduled, "m/d/yy")) & " " &
(Format$(Me.txtScheduledStartTime, "h:nn AM/PM"))
.ActiveDocument.Bookmarks("ContactName").Select
.Selection.Text = (CStr(txtWorkOrderContact & vbNullString))
.ActiveDocument.Bookmarks("Fax").Select
.Selection.Text = (CStr(Me.cboAccountID.Column(9)))
.ActiveDocument.Bookmarks("WorkOrder").Select
.Selection.Text = (CStr(Me.txtInvoiceNumber))
.ActiveDocument.Bookmarks("Problem").Select
.Selection.Text = (CStr(Me.txtProblemOrService & vbNullString))
.ActiveDocument.Bookmarks("Tech").Select
.Selection.Text = (CStr(Me.cboScheduledTech.Column(1) &
vbNullString))
' Debug.Print rs.RecordCount
If Not rs.BOF And Not rs.EOF Then
With CreateTableFromRst( _
objWord.ActiveDocument.Bookmarks("ItemDetails").Range, rs,
False)
'Apply formatting
.Range.Columns(1).Width = 29
.Range.Columns(2).Width = 337
.Range.Columns(3).Width = 140
objWord.Selection.MoveDown
End With
End If
' Cleanup
Set objWord = Nothing
Here's the code to create the table from the recordset:
Function CreateTableFromRst( _
rngAny As Word.Range, _
rstAny As ADODB.Recordset, _
Optional fIncludeFieldNames As Boolean = False) _
As Word.Table
Dim objTable As Word.Table
Dim fldAny As ADODB.Field
Dim varData As Variant
Dim strBookmark As String
Dim cField As Long
' Get the data from the recordset
varData = rstAny.GetString()
' Create the table
With rngAny
' Creating the basic table is easy,
' just insert the tab-delimted text
' add convert it to a table
.InsertAfter varData
Set objTable = .ConvertToTable()
' Field names are more work since
' you must do them one at a time
'If fIncludeFieldNames Then
'With objTable
' Add a new row on top and make it a heading
'.Rows.Add(.Rows(1)).HeadingFormat = True
' Iterate through the fields and add their
' names to the heading row
'For Each fldAny In rstAny.Fields
' cField = cField + 1
' .Cell(1, cField).Range.Text = fldAny.Name
'Next
'End With
'End If
End With
Set CreateTableFromRst = objTable
End Function
