"FMDev" wrote in message
...
Hello

I'm new to creating queries with Excel, and could do with some help.

Basically, I have a worksheet with about 200 rows of data. One of the
columns is empty and I want to populate this column with data from a
Filemaker Pro database.

I have created an ODBC link to the database via MS Query, and can access
the
field I need, however, I am struggling to find a way to match the data in
the database to the rows in the worksheet.

I would like pull over the matching data based on a unique reference
number
present in the worksheet and database, but I don't seem able to link the
two
in that way.


Hi FMDev,

I have a Database with a Table Experiments
That Table contains both a KeyExperiments and a CodeExperiment
I have the CodeExperiment , input by the user , but need the KeyExperiments
Here is the part of the code that works for me :

1) Create a New ADODB.RECORDSET
2) Use the Find function on the recordset to retrieve the record from the
database ; the construction of the criteria for the find function is a bit
tricky ....

Dim strSQL As String
Dim rcsExperiments As New ADODB.Recordset
Dim intKeyExperiments As Long
Dim strCriteria As String
Dim strCodeExperiment As String



'
'Open the Table Experiments in a Recordset
'to lookup the KeyExperiments for the Experiments
'with CodeExperiment
strSQL = "SELECT KeyExperiments , CodeExperiment FROM Experiments ;"
'
rcsExperiments.Open strSQL, _
"Provider=MSDASQL;DSN=KinomicsDSN", adOpenStatic, adLockOptimistic


'Lookup the next KeyExperiments
'Mark the sequence : Double quote , single quote , double quote to enter
'the CodeExperiment in the search string as a string !!
strCriteria="CodeExperiment = " & "'" & strCodeExperiment & "'"
rcsExperiments.MoveFirst
On Error GoTo NotFound
rcsExperiments.Find (strCriteria)
intKeyExperiments = rcsExperiments.Fields("KeyExperiments")
On Error GoTo 0

hth
Gys