UDF alternative to PULL that opens external workbook and grabs val

[Shawn]

Hello,

I have been using Harlan Grove's PULL to reference values from a closed
workbook and it is very good for what it does. But unfortunately it is not
too practical for referencing large data ranges, as calculations can take a
long time.

I was wondering if there exists a similar UDF that doesn't use the
ExecuteExcel4Macro logic, but instead actually opens the external workbook,
grabs the values it needs (using a cell range or a named range), closes the
external workbook and returns a Variant (range or single value).

I don't even know if this type of thing (opening workbooks, etc.) is
possible to do in a UDF.

Thanks,
Shawn

 

[Harlan Grove]

Shawn wrote...

I have been using Harlan Grove's PULL to reference values from a closed
workbook and it is very good for what it does. But unfortunately it is not
too practical for referencing large data ranges, as calculations can take a
long time.

I was wondering if there exists a similar UDF that doesn't use the
ExecuteExcel4Macro logic, but instead actually opens the external workbook,
grabs the values it needs (using a cell range or a named range), closes the
external workbook and returns a Variant (range or single value).

I don't even know if this type of thing (opening workbooks, etc.) is
possible to do in a UDF.

Your alternatives include Laurent Longre's MOREFUNC.XLL add-in,
specifically, it's INDIRECT.EXT function, and using macros to build
direct external reference links.

INDIRECT.EXT works in a similar way to pull, by using another Excel
application instance to fetch values from closed workbooks. It could
perhaps speed things up a bit, but likely not substantially.

Macros that build direct external references links would likely provide
fastest recalculation. If you're willing to use a lot of ancillary
cells, the most recalc-efficient approach would be to fill a range in
your destination workbook with an array formula referring to your large
external range in your source workbook.

 

[Charles Williams]

Shawn,

The most efficient way is to open all the closed workbooks: given the amount
of RAM most Pc's have this is (IMHO) the way to go unless you have a very
old PC (<=64MB RAM) and a very old version of Excel (Excel97 or 2000)

regards
Charles