connect Excel to external database

Wally-sns

New Member
Joined
Jun 30, 2006
Messages
46
I need to lookup a column of values in an external database....i went through all the microsoft queries with my IT guy, but nothing seems to work.

Again, my list of values to be looked up is in excel, and i need to find them in an external database.

Any help or experience with this is appreciated .
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
You can either:

1) MSQuery them one at a time
2) Import from your external database first (assuming you can filter/limit fields/etc. to get the import to a managable size), then match against the import
3) Use Access or SAS or some other database software to do your merge
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
So you effectively want to use the external database like the range argument in VLOOKUP? What kind of external DB is it? Access or something else. How many values fo you wish to look up?
 

Wally-sns

New Member
Joined
Jun 30, 2006
Messages
46
Hey oaktree

1) cant do one at a time...there are 1,000 entries at any given point
2) dont want to import due to size of the data...it would probably be 250,000 lines
3) dont understand

Then what is the purpose of "link to external database" if you cant use the external database as source data ?
 

Wally-sns

New Member
Joined
Jun 30, 2006
Messages
46

ADVERTISEMENT

Richard Scholar....yes, you are right...i want to use that external database as a vlookup source.

I need to look up like 1,000...but values to lookup will change each month.

The database i am trying to use is oracle, with the business objects overlay
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
Actually, backing up a step, I think you're going to have to look into ODBCs so that you can communicate with your Oracle database. Your IT person should be able to help with that if your ODBC is not already set up.

Once your ODBC is set up, given the size of your database, you're either going to have to use a database program (such as Access) to link to your database and merge. Or, try searching the board for ADO, as there are a couple of examples for how you can use SQL (outside my forte) to pull from the source.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,033
Members
410,583
Latest member
gazz57
Top