Paste or Insert Recordset into MS Word Table

I would like to be able to paste or insert data from an adodb recordset into an MS Word (2003).  I have a Word template that contains several tables that I populate one row at a time from a recordset.  The tables start out with two rows, a header row, and a blank row.  The first blank row is populated, field by field, then a new row is inserted as needed.  This works at an acceptable speed unless the recordset contains several thousand records.
For recordsets that I don't want to modify, is there a way to insert the entire recordset (excluding the header) into a table, starting at row 2?  Can it add additional rows automatically?
ddelauterAsked:
Who is Participating?
 
irudykConnect With a Mentor Commented:
Try the following which will go through each row in a table and set the width of each cell to the width set on row 1 of its respective column.
 


Set dtable = ActiveDocument.Tables(10)
 
Dim sTemp As String
sTemp = adorst.GetString(2, -1, Chr(9))
 
'code to go to the end of the table heading row
dtable.Select
Selection.MoveDown Unit:=wdLine, Count:=1 'THIS WORKS THE CLOSEST
 
'Insert the data into the Word document
Selection.Text = sTemp
 
'Convert the text to a table and format the table
Selection.ConvertToTable Chr(9)
 
Dim c As Cell
For Each c In ActiveDocument.Tables(10).Range.Cells
    c.Width = ActiveDocument.Tables(10).Cell(1, c.ColumnIndex).Width
Next c

Open in new window

0
 
irudykCommented:
Looking at http://support.microsoft.com/kb/261999 you could use a similar code.  For example:
Sub YourRoutine()
 
'code to get ADO recordset (rs Object)
 
Dim strTemp As String
sTemp = rs.GetString(2, -1, Chr(9))
 
'code to go to the end of the table heading row  
Selection.EndKey wdStory
 
'Insert the data into the Word document
Selection.Text = sTemp
 
'Convert the text to a table and format the table
Selection.ConvertToTable Chr(9)
 
'Remainder of you code here
 
End Sub

Open in new window

0
 
ddelauterAuthor Commented:
Thanks.  I will massage this and see if it works.
0
 
ddelauterAuthor Commented:
irudyk,
I've tried this a few ways.  The best result so far (using code below) gives me a table, attached to the existing table header row, but the format is not inherited by the new rows (see attached sample.gif).  I could add some code to reformat, but I would like to avoid that since I have several different tables wih different layouts.
Using Selection.Endkey creates a table within a table (see sample2.gif).  Also, I removed wdStory from Selection.EndKey.  That placed the cursor at the bottom of the entire document.
I've also tried adding a row, selecting row before paste, etc, but no joy.
I have also performed the code below, then cut the data from the new table, deleted all of the rows, inserted a new row below the existing table, then pasted the data.  This did work, but I am not sure all of that would speed up the process (and it seems like a messy method).
Thanks. DD


Set dtable = ActiveDocument.Tables(10)
Dim sTemp As String
sTemp = adorst.GetString(2, -1, Chr(9))
'code to go to the end of the table heading row
dtable.Select
Selection.MoveDown Unit:=wdLine, Count:=1 'THIS WORKS THE CLOSEST
'Selection.EndKey
'Insert the data into the Word document
Selection.Text = sTemp
 
'Convert the text to a table and format the table
Selection.ConvertToTable Chr(9)
 
'Remainder of you code here

Open in new window

sample.gif
sample2.gif
0
All Courses

From novice to tech pro — start learning today.