Vlookup on a file without opening it?

[pkmoore]

Can I use VB to do a vlookup or equivalent to find data in an external file with out first opening that external file (file is to big).

The external file is a csv file. Normally I'd open it then do a vlookup against it. Since the file is 150,000 rows I can't do that. So I'd like to write a function in VB to fetch the data for each cell in a column. I tried using an external data query but I need every cell in a column to return unique data for that cell and I don't know how to pass a list of part numbers to an external query without doing each cell one at a time.

Please help![/b]

 

[ETID]

Have you tried the ODBC text file driver...

in a blank excel worksheet, from the menu...

Data>get external data>new database query, choose text files from the list...follow the wizard, or select the use MS query option,...carefully select options you want on each window,...such as "read only" check boxes and what not...
then return data to ms excel.

when the data is returned, you can right click in the data area and set properies such as refresh on open


now when ever the database is updated the workbook will reflect the changes.(if you refresh...auto or manually)

If you run the macro recorder while ...editing the query...you can then see the sql statement in the vba editor, then you can splice variables into the string and run updates via vba, launched either on open or from some kind of user event.

 

[pkmoore]

Thanx, but I'm not making myself clear in my needs. I have asked this question before and can't seem to get the answer I'm looking for.

Yes, I've tried that.

Here's the deal.

I have a database that has 50,000 records in it that contain things like part_number, Cost, Price, Descriptions, etc.

Now some one sends me a spread sheet that has 100 part numbers on it and they want the cost associated with those. I want to click a macro button to automatically go get the cost (in this case) for each of the part numbers. I have written VB to do this but it opens the database fetches the first part number and returns the cost and closes the database, moves on to the next cell and does it all again, 100 times. VERY SLOW.

What I want: Open the database, pass the entire list (column) of part numbers and "join" the list with the table in the database. And return then entire dataset at once.

The next time someone might send me a spreadsheet requesting descriptions with 500 different partnumbers.

Hope that is clearer on what I am trying to do.
I don't want to have to anything but click a button, a form pops up, I select the field to return. and woosh! it's done.

Thanks!!

 

[xlbo]

Would it not be easier to do this within the database and then export to excel as necessary. If you are using Access, you can link a spreadsheet to a table in Access so whatever changes in the spreadsheet is reflected in the access table. This could be your set of part numbers
Set up a standard query that joins this linked table and your data table (make it a Make Table query)
Then you can have an export macro that will take that new table and export to excel. All of this can be automated FROM excel as most of it can be set up template style...worth a thought ??

AFAIK, the only way to get data from a CLOSED file is to use an old XLM macro and you have to specify the cell reference (which you don't know beforehand)
You CAN do parameter queries within MSQuery but I'm not sure what it's like handling different numbers of criteria as parameters Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[pkmoore]

Thanks, that is what I do now. I take the spreadsheet they send me, copy the cells (part numbers), past them into a table called Generic_PartNums and run an access query. Then paste into excel and do a vlookup from the output back to the part numbers. Can't just paste the output back in, in case of dups or missing records.

i.e. Get Cost or Get Description, etc.

I wanted to make this a tool that I could give to others that don't know a thing about access.

The problem I have with linking the spreadsheet to Access, is that the format of the spreadsheets I get sent to me are always different. The part numbers are in different columns, on different tabs, etc.

Your idea about a template style is interesting. (don't know a thing about that)

Can I create a macro (VB) with in excel to create the link of the current worksheet with access then run the access query (get external data query) with in excel, without every opeing access (invisible to user)???

Thanx

 

[Gavona]

I am out of my depth here but have a look within Excel at Data,GetExternalData,CreateNewQuery
I suspect that the people you give the tool to don't need Access at all.

 

[kaatjev]

my machine at work does not have the Data,GetExternalData,CreateNewQuery feature turned on. As this is a large corp, I doubt seriously they will ever turn it on.

I have a large file also - how do I do a vlookup on a closed file?

 

[xlbo]

did you not read the posts ?? you can't - well you can, but it will only update when you open the file it is looking up into...

As this is a large corp, I doubt seriously they will ever turn it on

why not ?? just get your IT dept to supply the MSOffice install disc and install the MSQuery add-in....that's what I did (and there ain't many corps in the world larger than the one I used to work for ;-))

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question