How to Obtain information from other workbooks

18829 views
Skip to first unread message

sdouglas <>

unread,
Feb 6, 2004, 4:04:42 AM2/6/04
to
I have a large number of workbooks that give a lot of detailed
information, and I need to obtain daily summary information from these
workbooks into a new workbook.

To do this, the user would have to enter in a code, which relates to
the excel filename.

I can build up the formula needed using vba code assigned to a button,
but this seems very "clunky" and prone to user error. Surely there must
be a simple existing function to do this.
Everything I search for appears to require the workbooks to be open,
which in this case is unsuitable.

Failing that, if I have to stick with VBA code, can code be assigned to
cells? I'm more used to VB then VBA.

Using Excel 2000.

Thanks in Advance.


---
Message posted from http://www.ExcelForum.com/

Frank Kabel

unread,
Feb 6, 2004, 6:18:04 AM2/6/04
to
Hi
I think you have tried the INDIRECT function and discovered the 'open
workbook issue'. One way is the use of the free-addin MOREFUNC.XLL
(http://longre.free.fr/english).
The function INDIRECT.EXT overcomes this restriction

Frank

Frank Kabel

unread,
Feb 9, 2004, 9:22:24 AM2/9/04
to
> Unfortunatly the INDIRECT.EXT does not work either :(. It behaves in
> exactly the same way as INDIRECT.
> I.e. if the workbook is not open, it won't retreive the value.

>
>
> ---
> Message posted from http://www.ExcelForum.com/

Hi
are you using INDIRECT.EXT in combination with other formulas (e.g.
VLOOKUP) or stand-alone. The latter one will work. Please post your
complete formula to give you some advise

Frank

sdouglas <>

unread,
Feb 9, 2004, 9:00:48 AM2/9/04
to
Unfortunatly the INDIRECT.EXT does not work either :(. It behaves in
exactly the same way as INDIRECT.
I.e. if the workbook is not open, it won't retreive the value.

Harlan Grove

unread,
Feb 9, 2004, 1:29:33 PM2/9/04
to
"Frank Kabel" wrote...

>>Unfortunatly the INDIRECT.EXT does not work either :(. It behaves in
>>exactly the same way as INDIRECT.
>>I.e. if the workbook is not open, it won't retreive the value.
..

>are you using INDIRECT.EXT in combination with other formulas (e.g.
>VLOOKUP) or stand-alone. The latter one will work. Please post your
>complete formula to give you some advise

It's not just when needing to return multiple cell ranges that INDIRECT.EXT
breaks down. It also doesn't dereference defined names, even those referring to
single cells, and there are some systems on which it just doesn't work. Look
through the archives from about a year ago, and you'll see a few threads in
which Laurent Longre and I discussed this problem.

For the OP, here's the complete list of alternatives, now updated to include
SQL.REQUEST. Frank has a peculiar aversion to the 4th one. Guess he doesn't want
to give complete answers.


1. Use formulas to create literal external reference formulas as text, e.g.,

="=SUMPRODUCT(('C:\somedir\"&SubDir&"\["&Filename&"]"&WorksheetName&"'!"&
FirstRangeAddress&"="&whatever&")*'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&SecondRangeAddress&")"

To convert these to formulas, copy the cells containing them and paste-special
as Values on top of themselves, then Edit > Replace, finding = and replacing
with = . Yes, replace the equal signs with themselves. This effectively enters
all of these as formulas. This is the most effective technique using only
built-in functionality, but if you change the subdirectory or base file names,
you'll need to repeat this procedure. Also, it may require a LOT of memory.


2. Try Laurent Longre's MOREFUNC.XLL add-in, which is avaialable at

http://longre.free.fr/english/

It provides a function called INDIRECT.EXT which would do what you want. It
works on some of the PCs I use, but not on one running Windows Me. Also, it
doesn't work with defined names in closed workbooks. You'd use it like

=SUMPRODUCT((INDIRECT.EXT("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&FirstRangeAddress)=whatever)
*INDIRECT.EXT("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&SecondRangeAddress))


3. Slow. Use SQL.REQUEST *if* the data you'd access looks like a database table,
i.e., it's in a single area, multiple cell *named* range with field names in the
top row. See the following linked thread for more details.

http://www.google.com/groups?threadm=O5aTScOyDHA.2084%40TK2MSFTNGP09.phx.gbl


4. Also slow, but it seems to be more robust than #2 and, unlike #3, imposes no
restrictions on data layout - a udf which uses a separate Excel application
instance to pull data from closed workbooks. The VBA code is in the linked
article.

http://www.google.com/groups?selm=sH1La.4495%24cJ5.436%40www.newsranger.com

Use it like

=SUMPRODUCT((pull("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&FirstRangeAddress)=whatever)
*pull("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&SecondRangeAddress))


Similar to #1, there's also John Walkenbach's GetValue function, but it's only
useful when called by macros. But for completeness,

http://j-walk.com/ss/excel/tips/tip82.htm

--
To top-post is human, to bottom-post and snip is sublime.

Frank Kabel

unread,
Feb 9, 2004, 1:42:09 PM2/9/04
to
Harlan Grove wrote:

> It's not just when needing to return multiple cell ranges that
> INDIRECT.EXT breaks down. It also doesn't dereference defined names,
> even those referring to single cells, and there are some systems on
> which it just doesn't work. Look through the archives from about a
> year ago, and you'll see a few threads in which Laurent Longre and I
> discussed this problem.

Thanks for the info.

> For the OP, here's the complete list of alternatives, now updated to
> include SQL.REQUEST. Frank has a peculiar aversion to the 4th one.
> Guess he doesn't want to give complete answers.

No, definetly no aversion! I like this solution but in most
circumstances it is not required. So if INDIRECT.EXT is not an option
the 4th solution would be my suggestion :-)
Though I will add this to my 'standard template' for answering INDIRECT
+ closed workbook related questions to ensure complete answers ;-)

Regards
Frank

Harlan Grove

unread,
Feb 9, 2004, 3:26:15 PM2/9/04
to
"Frank Kabel" wrote...
..
> . . . but in most circumstances it is not required. . . .

Begging the question whether most circumstances involve dereferencing a single,
unnamed cells rather than either multiple cell ranges or named, single area
ranges.

sdouglas <>

unread,
Feb 11, 2004, 5:41:20 AM2/11/04
to
The formula is just using the INDIRECT.EXT by itself.

=INDIRECT.EXT("'C:\somedir\["&B2&".xls]Summary Sheet'!$E$5")

Reply all
Reply to author
Forward
0 new messages