asked on

VBA copy Access recordset to existing Word table

Dear Expert Exchange,

I am using Office 2003
I manage to generate a recordset from query to Excel worksheet and everything works fine, but instead of Excel Worksheet, I require to transfer the recordset to an existing Word document where it contain 2 tables, the recordset is suppose to go to table 2 where table 2 already formatted with Field Headings, the recordset should starts to fill starting from Row1, row data from recordset is dynamic.

Following is the vb code I use to post the recordset to Excel without problem.

Private Sub Command52_Click()
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim dbs As DAO.Database
Set dbs = CurrentDb
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(strSheetName) > 0 Then
xlWSh.Name = Left(strSheetName, 34)
End If

Set rst = dbs.OpenRecordset("Mytble", dbOpenDynaset)
xlWSh.Activate
xlWSh.Range("B4").Select ' Header Posting
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("B4:H4").Font.Bold = True ' Bold Header
xlWSh.Range("B5").CopyFromRecordset rst ' Data Posting
xlWSh.Rows.AutoFit
xlWSh.Columns.AutoFit

rst.Close
Set rst = Nothing

End Sub

Please show me how to post the same recordset to Word table line by line as I am new in vb programming.

Thanks

Rgonzo1971

Hi,

pls try

Sub ExportToWord()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblMyTable")
Set Wd = New Word.Application
Set myDoc = Wd.Documents.Open("c:\aa.doc")

Wd.Visible = True
i = 0
Do Until rs.EOF
    myDoc.Tables(1).Columns(1).Cells(i + 1).Range.Text = rs.Fields(0)
    myDoc.Tables(1).Columns(2).Cells(i + 1).Range.Text = rs.Fields(1)

i = i + 1
Loop


End Sub

Open in new window

Regards

kaysoo

ASKER

Hi Rgonzo1971,

Thank you very much for your reply. It almost got it but no quite, the recordset did not copy into the table as intended.

I have attached two jpg files for clearer picture for explanation; on daily basis, data from AccessTable generate dynamic Rows of data, what I intended is to append the recordset from AccessTable to Word Table as shown Row by Row, Word Table already Pre-formatted with Row Heading, Word Table should insert new row to accommodate the recordset if require.

Thank you for your help.
AccessTable.jpg
WordTable.jpg

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kaysoo

ASKER

Tq vy much for your solution, fantastic !!!! It works like a charm !!!

Bravo...