Solved

Paste or Insert Recordset into MS Word Table

Posted on 2008-10-03
4
862 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
ID: 22639605
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
ID: 22639632
Thanks.  I will massage this and see if it works.
0
 

Author Comment

by:ddelauter
ID: 22711904
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
ID: 22712188
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

932 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

9 Experts available now in Live!

Get 1:1 Help Now