I'm trying to create a macro that will examine the active workbook and
generate a new sheet listing any cells that contain links to external
workbooks. I know that I can see what the links ARE, as in the name and path
of linked workbooks. What I'd like to do in addition to this is see what
cells use those links.

For instance, I already have code that can tell me that a given workbook has
a link to the file C:\external.xls

But suppose that in the active workbook, Sheet1 Cell D15 has the function
"=HLOOKUP($E$15,'C:\[external.xls]Sheet3'!$D$4:$L$25,A23,FALSE)"

I'd like to include that cell reference (and ideally, that function) in the
report. Short of examining every cell in the workbook for a function that
contains "C:\external.xls", I don't know of any other methods. I'd appreciate
any suggestions. Thanks.
--
Hmm...they have the Internet on COMPUTERS now!

Try this macro:

Sub bigmac()
Dim s, s2 As String
Dim rr, r As Range

s = "external.xls"
Set rr = Nothing

For Each r In Selection
If r.HasFormula Then
s2 = r.Formula
If InStr(1, s2, s) = 0 Then
Else
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Else
End If
Next
rr.Select
End Sub


You select some cells and run the macro. It should look thru your
selection. If it finds a cell containing a formula, it looks inside the
formula ( as a string) for the string "external.xls". If the desired
sub-string is there, it adds the cell to a growing range thru UNION().

Finally it selects the union.
--
Gary's Student


"MDW" wrote:

I'm trying to create a macro that will examine the active workbook and
generate a new sheet listing any cells that contain links to external
workbooks. I know that I can see what the links ARE, as in the name and path
of linked workbooks. What I'd like to do in addition to this is see what
cells use those links.

For instance, I already have code that can tell me that a given workbook has
a link to the file C:\external.xls

But suppose that in the active workbook, Sheet1 Cell D15 has the function
"=HLOOKUP($E$15,'C:\[external.xls]Sheet3'!$D$4:$L$25,A23,FALSE)"

I'd like to include that cell reference (and ideally, that function) in the
report. Short of examining every cell in the workbook for a function that
contains "C:\external.xls", I don't know of any other methods. I'd appreciate
any suggestions. Thanks.
--
Hmm...they have the Internet on COMPUTERS now!

Gary''s Student wrote...
Try this macro:

Sub bigmac()
Dim s, s2 As String
Dim rr, r As Range

s = "external.xls"
Set rr = Nothing

For Each r In Selection
If r.HasFormula Then
s2 = r.Formula
If InStr(1, s2, s) = 0 Then
Else
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Else
End If
Next
rr.Select
End Sub


You select some cells and run the macro. It should look thru your
selection. If it finds a cell containing a formula, it looks inside the
formula ( as a string) for the string "external.xls". If the desired
sub-string is there, it adds the cell to a growing range thru UNION().
....

Should your macro include cells containing the following formulas?

="This is a mistake: "&"external.xls"

or

=external.xls+3

since external.xls is a syntactically valid defined name. There's also
the outside chance that the OP was oversimplifying and using
C:\[external.xls] as a token representing any filename. But getting
back to the points above, your macro should only include cells in which
external.xls is part of an external reference. There are two valid
forms:

'C:\[external.xls]SheetX'!Y99

and

'C:\external.xls'!foobar

That is, the filename appears in square brackets when followed
immediately by a worksheet name, and it appears without square brackets
when it refers to a workbook-level defined name or to a range in a
single worksheet .XLS file in which the worksheet name is the same as
the base filename (i.e., the filename without the .xls extension). And
it must not be within a double-quoted string.

Complicating matters A LOT is the fact that external.xls is also a
valid worksheet name. Try this.

1. Create a new workbook, delete all but one worksheet, name that one
worksheet external, enter foo in cell A1, and save the workbook as
external.xls. Leave it open.

2. Create another workbook, make sure it has at least two worksheets,
name one of them external.xls, enter bar in cell external.xls!A1, and
switch to the other worksheet.

3. In the other worksheet in the second workbook enter the formulas

A1:
=external.xls!A1

A2:
=[external.xls]external!A1

The first will refer to worksheet external.xls cell A1 in the same
workbook. The latter will refer to cell A1 in the only worksheet of the
other workbook, which is named external.xls. After you enter these
formulas, Excel shows them as *IDENTICAL*, both appearing in the
formula bar as

=external.xls!A1

But the OP showed links into closed workbooks, and that adds a path
component before the filename, so easier to locate.

Run a search (either on this newsgroup or Internet) for Bill Manvilles
FindLink.xla add-in.



"MDW" wrote:

I'm trying to create a macro that will examine the active workbook and
generate a new sheet listing any cells that contain links to external
workbooks. I know that I can see what the links ARE, as in the name and path
of linked workbooks. What I'd like to do in addition to this is see what
cells use those links.

For instance, I already have code that can tell me that a given workbook has
a link to the file C:\external.xls

But suppose that in the active workbook, Sheet1 Cell D15 has the function
"=HLOOKUP($E$15,'C:\[external.xls]Sheet3'!$D$4:$L$25,A23,FALSE)"

I'd like to include that cell reference (and ideally, that function) in the
report. Short of examining every cell in the workbook for a function that
contains "C:\external.xls", I don't know of any other methods. I'd appreciate
any suggestions. Thanks.
--
Hmm...they have the Internet on COMPUTERS now!