Help - Does value exist in range of CLOSED workbook?

[c mateland]

What is the best method to validate a string's existance in a CLOSED
workbook that has many filled cells?

I need to check for a string in column A of an external closed book.
Column A of the external book has over 47,000 filled cells. I have a
VBA routine that writes a match function into a cell of thisworkbook,
but I get the error alert, "Excel cannot complete this task with
available resources..." when the external book is closed. However, it
works fine when it's open.

I've tried both the countif and match function but both fail when the
external book is closed.

(Excel 2003)

Thank you for your help.
-Chuck

 

[Ron de Bruin]

Hi c mateland

The Vlookup worksheet function is working in a closed file

 

[JMB]

It would be much appreciated if you did not multipost.

http://cpearson.com/excel/newposte.htm

 

[c mateland]

Actually, I've tried the vlookup as well and it failed too.

All of these functions work (minus countif) with closed files. BUT
none of them work when the closed file contains 47000 rows in the
lookup range, is what I found.

Can you get such a vlookup to work with a closed workbook containing
47000 rows?

Thanks,
-Chuck

 

[c mateland]

Please disregard this thread in this group. I mistakenly posted it
here when I meant to post it in Programming.

-Chuck

 

[T. Valko]

It may have something to do with available memory.

My machine has 256mb ram (WinXP)

I created a file named TEST.XLS

I filled column A (A1:A65536) with the respective row numbers and filled
column B (B1:B65536) with random numbers.

Opened a new file. Entered this simple formula:

=VLOOKUP(A1,'C:\TV\[test.xls]Sheet1'!A:B,2,0)

It works just fine as long as TEXT.XLS is open. When I close TEXT.XLS I get
a popup message to the effect: Excel cannot complete this operation with
available resources......

TEST.XLS closes but the lookup formula returns a #N/A error if I use a
lookup value >16375.

I've been thinking about upgrading my ram but what I really need is a new
machine altogether! Got this one in 2001.

Biff

 

[c mateland]

Don't bother upgrading your RAM for this purpose. I don't think it
will matter. I think it's just a limit of Excel working with external
files. The amount of resources needed by Excel (if there truly is an
amount to be successful) is impractical. I'm working with 2GB of RAM
and a huge clean hard drive on a new beefy machine, and I still get
the alert.

Even if I could buy a Cray super computer and make this work, what
good would that be? My users wouldn't have such a miracle computer,
and so it would still fail on them.

-Chuck

It may have something to do with available memory.

My machine has 256mb ram (WinXP)

I created a file named TEST.XLS

I filled column A (A1:A65536) with the respective row numbers and filled
column B (B1:B65536) with random numbers.

Opened a new file. Entered this simple formula:

=VLOOKUP(A1,'C:\TV\[test.xls]Sheet1'!A:B,2,0)

It works just fine as long as TEXT.XLS is open. When I close TEXT.XLS I get
a popup message to the effect: Excel cannot complete this operation with
available resources......

TEST.XLS closes but the lookup formula returns a #N/A error if I use a
lookup value >16375.

I've been thinking about upgrading my ram but what I really need is a new
machine altogether! Got this one in 2001.

Biff

Actually, I've tried the vlookup as well and it failed too.

All of these functions work (minus countif) with closed files. BUT
none of them work when the closed file contains 47000 rows in the
lookup range, is what I found.

Can you get such a vlookup to work with a closed workbook containing
47000 rows?

- Show quoted text -

 

[Harlan Grove]

It may have something to do with available memory.

....

Not necessarily.

Opened a new file. Entered this simple formula:

=VLOOKUP(A1,'C:\TV\[test.xls]Sheet1'!A:B,2,0)

It works just fine as long as TEXT.XLS is open. When I close
TEXT.XLS I get a popup message to the effect: Excel cannot
complete this operation with available resources......

Excel passes '[test.xls]Sheet1'!A:B as a reference to a range in an
open workbook. OTOH, Excel passes 'C:\TV\[test.xls]Sheet1'!A:B as an
array (perhaps as a reference to an array), but to do so it needs to
create that array by reading the values from the closed workbook.
Since Excel 2003 & prior can't handle arrays spanning 65536 or more
rows, Excel can't handle this operation referencing a closed file.

This operation fails if you use A1:B65535. Heck, it fails if you use
A1:B16376. *BUT* it works without complaining if you use A1:H16375. So
it seems to me this is an old Excel 5 & prior limitation on array size
(number of rows) returned by external references into closed workbooks
that was never updated for the larger grid size in Excel 97 and later.
It has nothing to do with available memory, since most PCs, even those
bought around 2000, would have page files large enough to store arrays
of a hundred thousand rows by a dozen columns. This limitation is
hardcoded into Excel's own source code, just like 7 nested function
calls in cell formulas, 30 arguments in function calls, 255 characters
to define names.

IOW, buy a new machine with more RAM, and you'll still have this
problem.

 

[c mateland]

Harlan, thank you very much for doing that research. At least I know
to stop trying now.

I ended up with a routine that opens and copies the external source
data into thisworkbook, which my formulas then work off of. It adds a
few seconds to the opening of the file, but a benefit is that after
copy, I don't have to worry about continual connectivity to the
external file as I change criteria variables.

-Chuck

It may have something to do with available memory.

...

Not necessarily.

Opened a new file. Entered this simple formula:

=VLOOKUP(A1,'C:\TV\[test.xls]Sheet1'!A:B,2,0)

It works just fine as long as TEXT.XLS is open. When I close
TEXT.XLS I get a popup message to the effect: Excel cannot
complete this operation with available resources......

Excel passes '[test.xls]Sheet1'!A:B as a reference to a range in an
open workbook. OTOH, Excel passes 'C:\TV\[test.xls]Sheet1'!A:B as an
array (perhaps as a reference to an array), but to do so it needs to
create that array by reading the values from the closed workbook.
Since Excel 2003 & prior can't handle arrays spanning 65536 or more
rows, Excel can't handle this operation referencing a closed file.

This operation fails if you use A1:B65535. Heck, it fails if you use
A1:B16376. *BUT* it works without complaining if you use A1:H16375. So
it seems to me this is an old Excel 5 & prior limitation on array size
(number of rows) returned by external references into closed workbooks
that was never updated for the larger grid size in Excel 97 and later.
It has nothing to do with available memory, since most PCs, even those
bought around 2000, would have page files large enough to store arrays
of a hundred thousand rows by a dozen columns. This limitation is
hardcoded into Excel's own source code, just like 7 nested function
calls in cell formulas, 30 arguments in function calls, 255 characters
to define names.

IOW, buy a new machine with more RAM, and you'll still have this
problem.

 

[T. Valko]

Thanks, Harlan. Very useful info.

Here's what I tried as a workaround:

TEST.XLS Sheet1 column A filled with the respective row numbers.

New file:

A1 = 35000

Formula:

=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A1:A15000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A15001:A30000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A30001:A45000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A45001:A60000,0),MATCH(A1,'C:\TV\[test.xls]Sheet1'!A60001:A65536,0))

This still fails as described earlier when TEST.XLS is closed.

So I broke it down into a series of individual formulas:

=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A1:A15000,0))
=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A15001:A30000,0))
=COUNT(MATCH(A1,'C:\TV\[test.xls]Sheet1'!A30001:A45000,0))
etc
etc

Even this fails!

Biff

It may have something to do with available memory.

...

Not necessarily.

Opened a new file. Entered this simple formula:

=VLOOKUP(A1,'C:\TV\[test.xls]Sheet1'!A:B,2,0)

It works just fine as long as TEXT.XLS is open. When I close
TEXT.XLS I get a popup message to the effect: Excel cannot
complete this operation with available resources......

Excel passes '[test.xls]Sheet1'!A:B as a reference to a range in an
open workbook. OTOH, Excel passes 'C:\TV\[test.xls]Sheet1'!A:B as an
array (perhaps as a reference to an array), but to do so it needs to
create that array by reading the values from the closed workbook.
Since Excel 2003 & prior can't handle arrays spanning 65536 or more
rows, Excel can't handle this operation referencing a closed file.

This operation fails if you use A1:B65535. Heck, it fails if you use
A1:B16376. *BUT* it works without complaining if you use A1:H16375. So
it seems to me this is an old Excel 5 & prior limitation on array size
(number of rows) returned by external references into closed workbooks
that was never updated for the larger grid size in Excel 97 and later.
It has nothing to do with available memory, since most PCs, even those
bought around 2000, would have page files large enough to store arrays
of a hundred thousand rows by a dozen columns. This limitation is
hardcoded into Excel's own source code, just like 7 nested function
calls in cell formulas, 30 arguments in function calls, 255 characters
to define names.

IOW, buy a new machine with more RAM, and you'll still have this
problem.