Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - How to check if value exists in closed workbook?
[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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - How to check if value exists in closed workbook?
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" wrote: [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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - How to check if value exists in closed workbook?
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 On Apr 15, 3:19 pm, "Ron de Bruin" wrote: Hi c mateland The Vlookup worksheet function is working in a closed file -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "c mateland" wrote in oglegroups.com... 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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - How to check if value exists in closed workbook?
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 On Apr 15, 4:26 pm, JMB wrote: 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..htmh...uin.nl/ado.htm "c mateland" wrote: [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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I check if a pop-up menu exists... | Excel Programming | |||
How to check from VBA if sheet exists? | Excel Programming | |||
Check if sheet exists in a closed workbook | Excel Programming | |||
check if worksheet exists | Excel Programming | |||
check if worksheet exists | Excel Programming |