how does one follow a cell link to another workbook file

[bmurphy]

Hello Excellers,

I have a worksheet cell that contains a formula that refers to a cell
in another workbook file.

I have a VBA macro that needs to follow that reference. That is, open
the referenced file, and go to the sheet of the referenced cell (so as
to get other things from the same sheet).

In the past I've simply extracted the file name from the cell formula
using string functions. But that only works with straightforward cell
links which simply refers to a cell in the other file. That is, the
cell formula only contains one file name in it somewhere.

Now I'd like to handle a situation where the cell formula is more
complicated. In this case the formula uses the Choose worksheet
function to pick a link to another file. This means the formula
contains more than a just one file name. So my method of using string
functions to extract the file name doesn't cut it anymore.

Is there any way to determine the file name of the cell being
referenced by the more complicated cell formula?

Thanks,

Brian Murphy
Austin, TX

 

[Mike Fogleman]

Brian, your string functions may still work. Because the Choose function
uses an index number to select an argument, then you will need to take that
value into consideration to determine which argument (reference) to extract
from the formula. For example if the formula is
=CHOOSE(B1,[Book1.xls]Sheet1!$A$1,[Book2.xls]Sheet1!$A$1) and the value of
B1=2 then extract the reference following the 2nd comma in the formula.
There is a direct relationship between the index number to Choose and the
arguments which are separated by commas.

Mike F

 

[bmurphy]

Thank you for the suggestion, Mike. I hadn't thought of that. That
will certainly work when the cell formula happens to use Choose, and
the arguments contain the file names in explicit fashion. But it might
not be using Choose, or the file names might come from yet other cells.

I'm going to try to rephrase my question better.

If the result of a formula in a cell produces a reference to a cell in
another workbook, how can one determine the name of that file, and the
worksheet in that file?

Excel itself does this because it successfully fetches the value in
that cell.

If I use Value or Evaluate, I just get the displayed contents of the
referenced cell. I think I'm looking for some clever use of Dependents
or Precedents, but I have been able to find anything that works. Or
maybe a way to use Evaluate on the cell formula, but to get a reference
to the cell that the formula result points to.

Thanks,

Brian