UDF alternative to PULL that opens external workbook and grabs val

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shawn - 17 Aug 2006 20:36 GMT

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

Reply to this Message

Harlan Grove - 17 Aug 2006 20:44 GMT

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
[quoted text clipped - 8 lines]
>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.

Reply to this Message

Charles Williams - 17 Aug 2006 22:12 GMT

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

> Hello,
>
[quoted text clipped - 17 lines]
> Thanks,
> Shawn

Reply to this Message