Hi
I am trying to lookup data in an oracle table based on a value in my excel spreadsheet. I am using the DBVLOOKUP function that I found on Google, and am trying to convert the connection string to use an ORACLE connection.
The code I have is as follows...

Dim adoCN As ADODB.Connection
Dim strSQL As String

Public Function DBVLookUp(TableName As String, _
                          LookUpFieldName As String, _
                          LookupValue As String, _
                          ReturnField As String) As Variant
    Dim adoRS As ADODB.Recordset
    Set adoCN = New Connection
    adoCN.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=myTNSname;User Id=myuser;Password=mypass;"
    adoCN.Open

    Set adoRS = New ADODB.Recordset
    strSQL = "SELECT DISTINCT" & LookUpFieldName & ", " & ReturnField & _
             " FROM " & TableName & _
             " WHERE " & LookUpFieldName & "=" & LookupValue & ";"
             
    adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
    If adoRS.BOF And adoRS.EOF Then
        DBVLookUp = "Value not Found"
    Else
        DBVLookUp = adoRS.Fields(ReturnField).Value
    End If
    adoRS.Close
End Function
The module does not show any errors, the SQL statement is correct, and it does seem to be connecting to the data. However I only get #value showing in my spreadsheet.
The function on the spreadsheet looks like..
=DBVLOOKUP("TableName","LookUpFieldName", $F$23,"ReturnField")
where $F$23 is the cell that is being referenced for lookup in the Oracle DB.

I cant see what's going wrong , if someone could point me in the right direction that would be a great help.

Thx