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

 

[whoneeds8]

I'm going to bump this for you because I'm looking for the same.

 

[Pat Hartman]

I think you're going to need OLE automation to accomplish this. Sounds like you probably want to automate Access from excel since the spreadsheet formats vary.

My two cents worth: Convert the spreadsheets to Access and be done with the problem.