Filter external data based on value in Excel?

I'm not really sure if this would go under "General" or "Programming", but here goes a shot. I've never really used Excel before for anything serious. Anyways, in my current job I'm MIS Director for a small online retailer, and most of my day consists of running various reports sent to me via Excel against either Access or SQL back-ends for business operations.

In Excel 2007 I know how to import data from an external source (in this case, an Access database, but more often than not it's against a SQL Server instance). However, my current problem is thus: I have an Excel spreadsheet with some 7,000 product IDs in them, and I wish to check their price and run some minor calculations. While for this particular project I could easily import it into Excel and run a query, I'm thinking long-term.

Is there any way to filter imported data (i.e. a WHERE clause) based on the value of an Excel column? e.g. tell Excel (in pseudocode): "Import data from this Access database, for each row match up the ProductID in the database with the value of column 'A', and insert its corresponding Price value into column 'B'". I'm not seeing a way to do this, and it would make my job so much easier if I didn't have to import into SQL Server or Access, run queries and export back to another Excel file.

Hi

Try this.

1) Access database c:\temp\test3.mdb
2) Table: access_table2
3) The table has 2 fields COL_1 and COL_2
4) Range A1:A3 has the Product IDs and these would be found in COL_1 of the database.

The code below will loop through the items in column A, and bring back the result from COL_2 for the match.

Sub bbb()
  Set cn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
    
  cn.Open "provider=microsoft.jet.oledb.4.0;data source = c:\temp\test3.mdb"
  
  For Each ce In Range("A1:A3")
    rs.Open "select COL_2 from access_table2 where COL_1 = " & ce.Value, activeconnection:=cn
    ce.Offset(0, 1).Value = rs(0)
    rs.Close
  Next ce
  
 

  Set rs = Nothing
  Set cmd = Nothing
  cn.Close
  Set cn = Nothing
End Sub

HTH

rylo