Get information from selected row in external excel file

[vjdb]

The idea is as follows: The first column of the external excel document contains employee names. This data is displayed in a word-userform to be precise in a listbox. I have done this via the following code:[vba]Option Explicit
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

Private Sub UserForm_Initialize()
Set db = OpenDatabase("D:\schuitemaker\excel en sql\testdata.xls", False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `LAND`")
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

ListBox1.ColumnCount = rs.Fields.Count

ListBox1.Column = rs.GetRows(NoOfRecords)

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub[/vba] But now i am stuck because i do not know how the get data which belongs to the selected employee (such as mobilenr and email) into the word document via bookmarks. This information is in the same row. Any idea how to start with this one? TIA!

gr VJDB

[vjdb]

Can no one give me a clue on this subject?

[vjdb]

Or is this simply not possible and should i use Access instead?

[lucas]

Probably because you give code that goes with a document with a userform and draws from an external file......who wants to re-create all of that to answer a question?......not me.

provide a sample of each file. Maybe someone will look at it. Most of us hit the forum when we have a minute or two to answer your questions and usually don't have time to re-create entire scenarios.

Have you searched the forum? There has been plenty of discussion on this issue here in the past.

[vjdb]

Thanks for the reply Lucas! I have searched the forum word.mvps and of course google. Just to clear things up i will outline my idea once more.
A data file with in A2 John Johnson and A3 his email A4 his profession and so on. When John Johnson is selected in the listbox the different bookmarks should be filled with the corresponding data. So BookmarkEmail should be filled with the data A3.
I have made a zip file with the two files (.xls file in C:\test). Thanks for the help!

[fumei]

One way:

Have three arrays (Name, Email and Profession). Build the arrays from the RecordSet. I think you will have to use three SELECT instuctions to do so, but I don't know.

Use the Name array to populate the Listbox - rather than rs.GetRows.

Now, when you select from the listbox you can use the ListIndex value of the selected item to get the matching value in the other arrays.

There is probably a better way. This is not my area of expertise.