For the following code to work without modification, you will need to:
1 Assign the name DataTable to a range of cells in an Excel spreadsheet
that has the name DataSource and is located in the C:\ drive
2. In the document, the DropDown FormField must have the bookmark name
DropDown1 assigned to it and there must be a Text formfield with the
bookmark name of Text1 in which data from the Excel spreadsheet will be
displayed.
3. The Entries in the DropDown formfield would be the same as the
contents of the cells in the first column of the DataTable range mentioned
above
4. That data that is to be inserted into the document is contained in the
second column of the DataTable range
5. A macro containing the following code is run on exit from the
DropDown1 formfield
Dim myDataBase As Database
Dim rs As Recordset
Dim myDrop As DropDown
Dim DataItem As String
' Get the data from the DropDown formfield
Set myDrop = ActiveDocument.FormFields("Dropdown1").DropDown
DataItem = myDrop.ListEntries(myDrop.Value).Name
'Open the spreadsheet
Set myDataBase = OpenDatabase("C:\DataSource.xls", False, False, "Excel
8.0")
' Access the data in the named range DataTable
Set rs = myDataBase.OpenRecordset("DataTable", dbOpenForwardOnly)
' Find the record in the first column that matches the DataItem
Do While Not rs.EOF
If rs.Fields(0) = DataItem Then
'Set the .Result of the Text1 formfield to the information from the
cell in the second column
ActiveDocument.FormFields("Text1").result = rs.Fields(1)
End If
rs.MoveNext
Loop
'Then close the database
rs.Close
myDataBase.Close
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
