How to get Excel results into Word

[Ed from AZ]

I generated two Excel worksheets of data for about 700 items. One has
one line of information for each item, the other has multiple lines
for each item. Each item has a single unique identifier. I need to
build a report in Word with the single-line results in a header and
the multi-line results below.

This would be easier in Access if I could build a report from two
tables with a linked key field - but the Powers That Be didn't see fit
to give me that tool. So I'm trying to make do with what I've got!

About the only thought I have so far is to create a Word template with
my header as a table and a bookmark below it. With the two Excel
worksheets, iterate through the single-line results and build an array
to populate the table cells, then capture the unique key and for each
entry in the multi-line results that matches the key, build an array
to dump into the bookmark. Save, close, open new, lather, rinse,
repeat.

Is there an easier way?

Ed

 

[Guest]

I've done something very similar a while back, I took an identical approach,
it takes a bit but your thought process is good.

 

[Russ]

I think a pivot table in Excel could arrange a report like you want, have
you posed this question in the Excel forum?
http://www.cpearson.com/excel/links.htm

 

[Guest]

I think this will give you what you want:

1) Create a UserForm
2) Create a ListBox
3) Create a ControlButton

Double-click the control button and paste this code into the module:
Private Sub CommandButton1_Click()

Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm1.Hide

End Sub



Sub UserForm_Initialize()

Dim i As Integer, Addressee As String

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

' Open the database
Set db = OpenDatabase("C:\Documents and
Settings\xxx\Desktop\Contacts.xls", False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List

' Cleanup
rs.Close
db.Close

'AddressBlock = db

Set rs = Nothing
Set db = Nothing


End Sub

Hope that helps,
Ryan---