ryguy7272
Mon Nov 05 21:56:00 PST 2007
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---
--
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
>
>