Query/Lookup in large list (text file)

[Jason Roth]

Hi All!

I've searched the Excel newsgroups for an answer to this question, but
I can't find an answer...

I have a large tab-delimited text file. It's 573,000 lines long, and
contains 6 columns of data (6 fields).

I would like to query that file from Excel. If it were only 20,000
lines long, I would include the data in my excel workbook and use a
VLOOKUP. But I cannot include the data, so I'm a bit stuck.

One thing I CAN do is set up my data file as a "Data Source" in
Windows Administrative Tools->Data Sources (ODBC), and query that data
source. The problems with doing that are (1) I'd like to distribute
the Excel file + the data file to other users, and unless they have
the same "Data Source", the query will not work for my users, and (2)
if I define the file as a data source, I cannot relocate the file
without screwing up the query.

My question is this: Is there a way to query my tab delimited text
file from VBA without setting my file up as a "Data Source"??...or how
do you simply query a text file from within XL?

Thanks for any ideas!!

--Jason

 

[Guest]

Have you thought about utilizing multiple tabs in a workbook for the data?
Then you can use the vlookup function and incorporate it with ISERROR
function or something to determine whether or not to look on another
worksheet.

-Chad

 

[Jason Roth]

That's not a bad idea, Chad.

I might have to do that.

The only problem is that doing that makes the filesize about 30
MB.

But that might be the best solution.

Thanks!--Jason