Query

[shrutikhurana]

Hi

I have one 80 mb file....and another 750 kb one in which i can enter one query and get all details from the 80 mb file . i c that it is a must for the 80 mb file to remain opened to get the data? Is there any other option by which I can just open the 750kb file and there is no need to hv the 80 mb file opened at that time?

Pls help!


Thanks

[flummi]

If in your 750 Kb file you run a database query you don't need to have
the 80 MB file loaded.

With a database query you can pull ANY ODBC source into your sheet
without a need to have the source loaded.

Did you try and got an error message? If so, which one?

Hans

[shrutikhurana]

never tried using database query....how does it work?

Thanks!

[flummi]

On your 80 MB file select the data range you want to import.
Click the name box (upper left part, next to the formula box).
Type a name for the data range.
Be sure to have column headers in Row 1 of your data range.

In a new workbook select e.g. A1
>From the main menu select data-->get external data-->ne database query
That will display a dialogue box. Select "excel files" and click ok
That will display a file open dialogue. Select the Excel file you need
(the 80 MB file)
On the next screen click the name you gave your data range, then click
the right arrow ">" to move the filed names into the right hand box.
Click next
On the next screen you can define selection criteria.
Click next
On the next screen you can define sort criteria
Click next
On the next screen select "view data in Query" and click finish
That will open MS Query
If you don't make any changes click "exit"
There are a few options on the next box which I would explain when you
want to go that route. For now click ok
Look t the results and let me know if it's what you can work with.

Hans

[shrutikhurana]

Actually my 80 mb file has 4 worksheets......2 have tables n all so the query thing can do but the remaining two have data spread in say 2 sections of the same worksheet....(in other words 2 tables side by side) for 2 worksheet. So any query looks for 6 tables......and yeah those 6 tables are very very huge with lots of columns.

vlookup works extremely fast to give me the data if the file is open.

[flummi]

What dows Excel say if you run your small workbook without the large
one being loaded?

I tried this and it worked fine:

=VLOOKUP(B1;'C:\[test1.xls]Sheet1'!$A$1:$B$4;2;FALSE)

without test1.xls being open.

Hans

[shrutikhurana]

apologies for the late follow up.

It is quite strange that the values to be displayed (using vlookup)appear initially when the base file is open....and not otherwise. but once i open and close it, it does display the output for sometime....but once i close the main enquiry sheet I again need to open the base file once. Don't understand why & how it works like this

any clues?