How to Obtain information from other workbooks

  • Thread starter Thread starter sdouglas
  • Start date Start date
S

sdouglas

I have a large number of workbooks that give a lot of detaile
information, and I need to obtain daily summary information from thes
workbooks into a new workbook.

To do this, the user would have to enter in a code, which relates t
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 mus
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 t
cells? I'm more used to VB then VBA.

Using Excel 2000.

Thanks in Advance
 
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
 
Unfortunatly the INDIRECT.EXT does not work either :(. It behaves i
exactly the same way as INDIRECT.
I.e. if the workbook is not open, it won't retreive the value
 
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.
Click to expand...

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
 
...
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
Click to expand...

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/[email protected]


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/[email protected]

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
 
Harlan said:
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.
Click to expand...

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.
Click to expand...

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
 
...
...
. . . but in most circumstances it is not required. . . .
Click to expand...

Begging the question whether most circumstances involve dereferencing a single,
unnamed cells rather than either multiple cell ranges or named, single area
ranges.
 
The formula is just using the INDIRECT.EXT by itself.

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