When I open a workbook (Excel 2000), I get the pop-up message that I have
external links. I cannot find the cell(s) that helped to produce this
message. However if I click on Edit, then Links, I find where the links are
going.

I need to find the cells that contain these links. I wrote some macros to
find formulas, but I get no hits. In the cases where I can find the cells,
the macros correctly lists these cells.

So, why can't I find these cells, and how do I find these cells.

I have included the code below:

Sub LastRow()
Dim LastRow As Long
Dim LastColumn As Long
Dim i As Integer
Dim objSht As Object

Open "C:\OnProcess\Special CRT Template Folder\FormulaList - " &
Mid(ActiveWorkbook.Name, 9, 5) & ".txt" For Output As #1

For i = 1 To Sheets.Count
Worksheets(i).Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Column - 1 +
ActiveSheet.UsedRange.Columns.Count
Print #1, "Tab Name: " & ActiveSheet.Name & " " & "Last Row: " &
LastRow & " " & "Last Column: " & LastColumn
listFormula LastRow, LastColumn
Next i

Close #1

End Sub


Function listFormula(LastRow As Long, LastColumn As Long)
Dim r As Integer
Dim intC As Integer
Dim strC As String
Dim a As Integer
Dim x As String

For r = 1 To LastRow
For intC = 3 To LastColumn
Select Case intC
Case 3
strC = "C"
Case 4
strC = "D"
Case 5
strC = "E"
Case 6
strC = "F"
Case 7
strC = "G"
Case 8
strC = "H"
Case 9
strC = "I"
Case 10
strC = "J"
Case 11
strC = "K"
Case 12
strC = "L"
Case 13
strC = "M"
Case 14
strC = "N"
Case 15
strC = "O"
Case 16
strC = "P"
Case 17
strC = "Q"
Case 18
strC = "R"
Case 19
strC = "S"
Case 20
strC = "T"
Case 21
strC = "U"
Case 22
strC = "V"
Case 23
strC = "W"
Case 24
strC = "X"
Case 25
strC = "Y"
Case 26
strC = "Z"
End Select
'If InStr(1, Range(strC & r).Formula, "=") = 1 Then
x = Range(strC & r).Formula
If InStr(1, Range(strC & r).Formula, "C:\") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
Print #1, strC & r & " " & " " & Range(strC & r).Value
End If
If InStr(1, Range(strC & r).Formula, "R:\") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
If InStr(1, Range(strC & r).Formula, "#REF") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
'End If
Next intC
Next r

End Function



thanks
--
Shell

I'd use Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

They may not be links that are in cells in formulas.

Shell wrote:

When I open a workbook (Excel 2000), I get the pop-up message that I have
external links. I cannot find the cell(s) that helped to produce this
message. However if I click on Edit, then Links, I find where the links are
going.

I need to find the cells that contain these links. I wrote some macros to
find formulas, but I get no hits. In the cases where I can find the cells,
the macros correctly lists these cells.

So, why can't I find these cells, and how do I find these cells.

I have included the code below:

Sub LastRow()
Dim LastRow As Long
Dim LastColumn As Long
Dim i As Integer
Dim objSht As Object

Open "C:\OnProcess\Special CRT Template Folder\FormulaList - " &
Mid(ActiveWorkbook.Name, 9, 5) & ".txt" For Output As #1

For i = 1 To Sheets.Count
Worksheets(i).Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Column - 1 +
ActiveSheet.UsedRange.Columns.Count
Print #1, "Tab Name: " & ActiveSheet.Name & " " & "Last Row: " &
LastRow & " " & "Last Column: " & LastColumn
listFormula LastRow, LastColumn
Next i

Close #1

End Sub

Function listFormula(LastRow As Long, LastColumn As Long)
Dim r As Integer
Dim intC As Integer
Dim strC As String
Dim a As Integer
Dim x As String

For r = 1 To LastRow
For intC = 3 To LastColumn
Select Case intC
Case 3
strC = "C"
Case 4
strC = "D"
Case 5
strC = "E"
Case 6
strC = "F"
Case 7
strC = "G"
Case 8
strC = "H"
Case 9
strC = "I"
Case 10
strC = "J"
Case 11
strC = "K"
Case 12
strC = "L"
Case 13
strC = "M"
Case 14
strC = "N"
Case 15
strC = "O"
Case 16
strC = "P"
Case 17
strC = "Q"
Case 18
strC = "R"
Case 19
strC = "S"
Case 20
strC = "T"
Case 21
strC = "U"
Case 22
strC = "V"
Case 23
strC = "W"
Case 24
strC = "X"
Case 25
strC = "Y"
Case 26
strC = "Z"
End Select
'If InStr(1, Range(strC & r).Formula, "=") = 1 Then
x = Range(strC & r).Formula
If InStr(1, Range(strC & r).Formula, "C:\") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
Print #1, strC & r & " " & " " & Range(strC & r).Value
End If
If InStr(1, Range(strC & r).Formula, "R:\") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
If InStr(1, Range(strC & r).Formula, "#REF") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
'End If
Next intC
Next r

End Function

thanks
--
Shell

--

Dave Peterson

Links can hide in a number of places. Charts, named ranges, pivot tables...
have you tried the findlinks addin.

http://www.oaltd.co.uk/MVP/
--
HTH...

Jim Thomlinson


"Shell" wrote:

When I open a workbook (Excel 2000), I get the pop-up message that I have
external links. I cannot find the cell(s) that helped to produce this
message. However if I click on Edit, then Links, I find where the links are
going.

I need to find the cells that contain these links. I wrote some macros to
find formulas, but I get no hits. In the cases where I can find the cells,
the macros correctly lists these cells.

So, why can't I find these cells, and how do I find these cells.

I have included the code below:

Sub LastRow()
Dim LastRow As Long
Dim LastColumn As Long
Dim i As Integer
Dim objSht As Object

Open "C:\OnProcess\Special CRT Template Folder\FormulaList - " &
Mid(ActiveWorkbook.Name, 9, 5) & ".txt" For Output As #1

For i = 1 To Sheets.Count
Worksheets(i).Activate
LastRow = ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Column - 1 +
ActiveSheet.UsedRange.Columns.Count
Print #1, "Tab Name: " & ActiveSheet.Name & " " & "Last Row: " &
LastRow & " " & "Last Column: " & LastColumn
listFormula LastRow, LastColumn
Next i

Close #1

End Sub


Function listFormula(LastRow As Long, LastColumn As Long)
Dim r As Integer
Dim intC As Integer
Dim strC As String
Dim a As Integer
Dim x As String

For r = 1 To LastRow
For intC = 3 To LastColumn
Select Case intC
Case 3
strC = "C"
Case 4
strC = "D"
Case 5
strC = "E"
Case 6
strC = "F"
Case 7
strC = "G"
Case 8
strC = "H"
Case 9
strC = "I"
Case 10
strC = "J"
Case 11
strC = "K"
Case 12
strC = "L"
Case 13
strC = "M"
Case 14
strC = "N"
Case 15
strC = "O"
Case 16
strC = "P"
Case 17
strC = "Q"
Case 18
strC = "R"
Case 19
strC = "S"
Case 20
strC = "T"
Case 21
strC = "U"
Case 22
strC = "V"
Case 23
strC = "W"
Case 24
strC = "X"
Case 25
strC = "Y"
Case 26
strC = "Z"
End Select
'If InStr(1, Range(strC & r).Formula, "=") = 1 Then
x = Range(strC & r).Formula
If InStr(1, Range(strC & r).Formula, "C:\") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
Print #1, strC & r & " " & " " & Range(strC & r).Value
End If
If InStr(1, Range(strC & r).Formula, "R:\") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
If InStr(1, Range(strC & r).Formula, "#REF") 0 Then
Print #1, strC & r & " " & " " & Range(strC & r).Formula
End If
'End If
Next intC
Next r

End Function



thanks
--
Shell