Break links in Excel 2000

[Randy]

I created a process using Excel 2002 and a step is to
break links (Edit/Links/select all links and click 'Break
Link') - however, the users only have Excel 2000 and
there is no 'Break Link' button in 2000.

Any suggestions on how to break the links in Excel 2000
with a single click?

Thanks,
Randy

 

[Dave Peterson]

If you know where they are, just copy|paste special|values.

If you don't know where they are: Get a copy of Bill Manville's FindLink
program:
http://www.bmsltd.ie/MVP/Default.htm

It'll help you find those links.

 

[Randy]

Right. This works, but with 30 sheets and many links per
sheet, it's a much slower process. Any suggestion as
straightforward as in 2003?

 

[Dave Peterson]

If the links are arranged nicely, just select that whole range and do Edit|copy,
Edit|paste special|Values.

If you've looked at your formulas with links, you'll see that they usually look
like:

='C:\My Documents\excel\[test1.xls]Sheet2'!$A$1

If all your worksheet formula links look like this, you can check to see if
there's a [, ], .xls, and ! in the formula. If you find all of them, it's
probably a link (but it doesn't have to be!).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

If your links always refer to the same workbook, you could add that condition to
the things to check.

And this'll get the links that are in formulas on worksheets. If you save your
workbook and reopen it and still get prompted for links, just use Bill
Manville's addin to clean up the remaining links. (They can hide in lots of
places--names and chart series for example).

 

[Guest]

ok. thanks.

-----Original Message-----
If the links are arranged nicely, just select that whole range and do Edit|copy,
Edit|paste special|Values.

If you've looked at your formulas with links, you'll see that they usually look
like:

='C:\My Documents\excel\[test1.xls]Sheet2'!$A$1

If all your worksheet formula links look like this, you can check to see if
there's a [, ], .xls, and ! in the formula. If you find all of them, it's
probably a link (but it doesn't have to be!).

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim rng As Range
Dim myCell As Range
Dim myStr As String

For Each wks In ActiveWorkbook.Worksheets
Set rng = Nothing
On Error Resume Next
Set rng = wks.UsedRange.Cells.SpecialCells (xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing--no formulas
Else
For Each myCell In rng.Cells
With myCell
myStr = LCase(.Formula)
If InStr(1, myStr, ".xls") > 0 _
And InStr(1, myStr, "!") > 0 _
And InStr(1, myStr, "]") > 0 _
And InStr(1, myStr, "[") > 0 Then
'maybe it's a link
.Value = .Value
End If
End With
Next myCell
End If
Next wks
End Sub

If your links always refer to the same workbook, you could add that condition to
the things to check.

And this'll get the links that are in formulas on worksheets. If you save your
workbook and reopen it and still get prompted for links, just use Bill
Manville's addin to clean up the remaining links. (They can hide in lots of
places--names and chart series for example).

Right. This works, but with 30 sheets and many links per
sheet, it's a much slower process. Any suggestion as
straightforward as in 2003?

--

Dave Peterson
(e-mail address removed)
.