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

I've done something very similar a while back, I took an identical approach,
it takes a bit but your thought process is good.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Ed from AZ" wrote:

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

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


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


--
Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

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.Insert After 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---

--
RyGuy


"Russ" wrote:

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


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


--
Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID