Look up values of Excel in Access table

[cosalex]

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

[mark007]

Something like the following in a code module:



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
=SalesValue(A1).

:)

[Zack Barresse]

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

[Ken Puls]

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,