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

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.

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

"Shawn" wrote in message
...

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