Help - How to check if value exists in closed workbook?

[c mateland]

[Sorry, I mistakenly posted this to the functions group. I meant to
post it here]

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

 

[Guest]

Not all of excel's functions can pull data from a closed workbook. See if
you can write your formula w/Sumproduct (note that sumproduct cannot use an
entire column, such as A:A, but A1:A65535 should work). For example, if you
want to search "Testbook!A1:A10" for "abc":

=SUMPRODUCT(--('I:\Excel\[Testbook.xls]Sheet1'!A1:A10="abc"))

or if you are searching for a substring, this appears to also work
=SUMPRODUCT(--(ISNUMBER(SEARCH("*abc*",'I:\Excel\[Testbook.xls]Sheet1'!A1:A10))))

or, if neither of those do what you need, perhaps you could pull the data
into an empty worksheet w/links, calculate what you need, then delete the
links:
='I:\Excel\[Testbook.xls]Sheet1'!A1
and copy it down.


Here are two other links I found from a previous post on pulling information
out of a closed workbook. If you search this site for "closed workbook" you
could probably find additional suggestions.
http://www.rondebruin.nl/copy7.htm
http://www.rondebruin.nl/ado.htm

 

[c mateland]

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

All of these functions work with closed files. I've established that
in my testing. 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]

The vlookup and match worked on closed workbooks, but not when the
lookup rows were 47000+. In fact, the sumproduct failed also in that
situation.

I wanted to avoid a routine to open and copy the 47000 rows of data
into my workbook every session just so Excel could run the formula.
But I guess that's what I'll have to do.

I didn't know Excel had a limitation of number of rows or data amount
it could negotiate with closed workbooks. Is there documentation of
such a limitation?

Thanks,
Chuck

Not all of excel's functions can pull data from a closed workbook. See if
you can write your formula w/Sumproduct (note that sumproduct cannot use an
entire column, such as A:A, but A1:A65535 should work). For example, if you
want to search "Testbook!A1:A10" for "abc":

=SUMPRODUCT(--('I:\Excel\[Testbook.xls]Sheet1'!A1:A10="abc"))

or if you are searching for a substring, this appears to also work
=SUMPRODUCT(--(ISNUMBER(SEARCH("*abc*",'I:\Excel\[Testbook.xls]Sheet1'!A1:A­10))))

or, if neither of those do what you need, perhaps you could pull the data
into an empty worksheet w/links, calculate what you need, then delete the
links:
='I:\Excel\[Testbook.xls]Sheet1'!A1
and copy it down.

Here are two other links I found from a previous post on pulling information
out of a closed workbook. If you search this site for "closed workbook" you
could probably find additional suggestions.http://www.rondebruin.nl/copy7..htmhttp://www.rondebruin.nl/ado.htm

[Sorry, I mistakenly posted this to the functions group. I meant to
post it here]

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- Hide quoted text -

- Show quoted text -