Look up values of Excel in Access table

Dear All,

I have a list of product codes in Excel (column A) and I need for each one of them to connect to an Access database and retrieve the corresponding sales value and have it in column B.

I have tried exporting the table from Access to Excel but then the vlookup takes a long time. The opposite, importing the product codes in Access, is even harder because I have 50 worksheets where the field name is not always the same and sometimes the lookup value is in different columns.

I was looking for a query on Access that would have as criteria-input the cells of Excel where the product codes are.

TIA,

cosalex

Something like the following in a code module:

Code:

---

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Const DB_PATH As String = "C:\mydb.mdb"

Sub Auto_Open()
    Set c = New ADODB.Connection
    c.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DB_PATH
End Sub

Sub Auto_Close()
    If c.State = adStateOpen Then
        c.Close
    End If
    Set c = Nothing
End Sub


Function SalesValue(ProductCode As String) As Double
If c.State = adStateClosed Then
        Auto_Open
    End If
Set r = c.Execute("select SalesValue from AccessTableName where ProductCode=" & ProductCode)
    SalesValue = CDbl(r.Fields(0).Value)
End Function

---

You will need to edit the SQL query slightly to fit your table and column names and also set the DB_PATH constant for your db. Additionally I have assumed that sales value is a decimal amount and used a double to return it. Change as appropriate.

You can then call the funcytion in colB using

Code:

---

=SalesValue(A1).

---

:)

Moved thread to Excel Help forum leaving Redirect in Access Help forum for cross-forum applicability.

FYI, in order to use Mark's code, you will need to set a reference to the "Microsoft ActiveX Data Objects Library (pick the most recent you have access to.)

HTH,