Query
 

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


--
shrutikhurana
------------------------------------------------------------------------
shrutikhurana's Profile: http://www.excelforum.com/member.php...o&userid=29868
View this thread: http://www.excelforum.com/showthread...hreadid=510466

Query
 

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

Query
 

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

Thanks!


--
shrutikhurana
------------------------------------------------------------------------
shrutikhurana's Profile: http://www.excelforum.com/member.php...o&userid=29868
View this thread: http://www.excelforum.com/showthread...hreadid=510466

Query
 

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

Query
 

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.


--
shrutikhurana
------------------------------------------------------------------------
shrutikhurana's Profile: http://www.excelforum.com/member.php...o&userid=29868
View this thread: http://www.excelforum.com/showthread...hreadid=510466

Query
 

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

Query
 

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?


--
shrutikhurana
------------------------------------------------------------------------
shrutikhurana's Profile: http://www.excelforum.com/member.php...o&userid=29868
View this thread: http://www.excelforum.com/showthread...hreadid=510466