Solved

Paste or Insert Recordset into MS Word Table

Posted on 2008-10-03
4
857 Views
Last Modified: 2011-10-19
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?
0
Comment
Question by:ddelauter
  • 2
  • 2
4 Comments
 
LVL 23

Expert Comment

by:irudyk
Comment Utility
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
 

Author Comment

by:ddelauter
Comment Utility
Thanks.  I will massage this and see if it works.
0
 

Author Comment

by:ddelauter
Comment Utility
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
 
LVL 23

Accepted Solution

by:
irudyk earned 500 total points
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now