Query Oracle using range in Excel to return individual records for each cell in range

Hey all,

First.. I hope I've put this thread in the right topic.. Wasn't quite sure where to put this one since it applies to Excel, databases and most likely VBA..

I've been looking for an answer for this for a long while.. I always seem to find a way around actually having to do it.. but, I don't think I can find a way around it this time..

Ok, here's my problem. I have 17,000 line items in an Excel spreadsheet. Each line is a particular part in a system that we've shipped to customers. For each line item, I need to pull data from a massive Oracle db in the sky that contains information specific to each part and put it in the correct column of each line item.

Example:

Part# Type Color
1234 ? ?
2341 ? ?
3412 ? ?

So, for example.. Ideally, I'd like to take the part number from the spreadsheet, query the db and then return the results for Type and Color to the spreadsheet.

I assume this can be done with VBA somehow, but I'm not even sure where to start. I'm pretty handy with VBA.. I can query the db from excel using Access or MS Query via ODBC.. but as far as how tie the two together in order to query the DB for numerous cells in a range using VBA, I haven't got a clue!

I've been all over Google hunting for the answer and have come close.. but not close enough..

Please lend me some knowledge!

Oh, and I'm using Excel 2007..

Thanks!

Oh... hmm.. not even sure where to check that.. I know if I hit the "Database Documenter" button in access, it comes up with "Version: 4.0", but that seems a bit low..??

Currently, I run pass-through queries via Access.. can do it in MS Query as well.. I can return the results of those queries to a data table in Excel.. but those are just general select queries written in a query editor.. no issues there:

SELECT PN, TYPE, COLOR
FROM PartTable

For what I need to do now, I think I will need to write a procedure to send a select statement for each part , using the part number as a parameter in the Select statement. I envision that the select statement is going to be string that I'll be concatenating the PN into for each row, which is a piece of cake. But then what?

In VBA, how do I connect to the db, send off select queries based on the part number for each line item, and then return the data from the db to the line items that the records belong to in the spreadsheet instead of a data table like it usually does.

Sure would be nice if I could just use a Where statement like this:

SELECT PN, TYPE, COLOR
FROM PartTable
WHERE PN = [Any of the part numbers in this gargantuan spreadsheet]

Sadly, I don't think that's going to work.. lol

I understand that my solution, if it is possible, is probably going to take forever for even VB to process it all.. but it's still better than me doing it by hand.. plus, I'm still kind of a database noob, so if anyone else knows of a better way to do this.. please jump in!

Thanks!

Cool.. and I can probably use the connection string from a properties dialog box for one of the data tables that I already have linked.

That just leaves the actual querying and return of data.. lol

Thanks Xlbiznes! That's a good start!

Wow.. wish I could have found this a lot sooner..

For others attempting to query DB's from Excel, follow the link below to be enlightened.. and it's from Microsoft..

If you know your way around VBA, this might be all you need.. unfortunately, if you are new to VBA.. there is very little explanation as to what the code does..

http://support.microsoft.com/kb/185125

Apparently, instead of Googling "query database from excel", I should have tried "Invoke a Stored Procedure with ADO Query Using VBA".. because that's exactly what someone who has no clue what to search for would type.. (sarcasm.. lol)