How to delete external links excel 2007

[coltaalex]

i have a workbook with 25 sheets, and each sheet has hundreds of formulas,
i tried the flowing VBA code, but is is running forever,
i any other way,

Sub Remove_External_Links_in_Cells()

Dim LinkCell
Dim FormulaCells As Range
Dim SheetsInWorkbook As Object

For Each SheetsInWorkbook In Sheets
SheetsInWorkbook.Activate
'*** Error trapping if no formulacells found
On Error Resume Next
'*** Select only formula cells
Set FormulaCells = Cells.SpecialCells(xlFormulas)
'*** Loop every formulacell in sheet
For Each LinkCell In FormulaCells
'*** If you want paste linked value as "normal value"
If InStr(1, LinkCell.Value, ".xls]") = 0 Then
LinkCell.Value = LinkCell
End If
Next
Next
End Sub

 

[coltaalex]

I going to name manager,
and there are links, but i think those links are internal, between sheets, not external,
Excel help is saying that all those links are external.
so now I'm deleting all of them to see what happens,




if those links are considered external links, i cannot delete them i need those.

 

[Gavona]

Named Ranges, No! I tested a referece to a named range in an external workbook and breaking links was active and worked.

A named range referring to an external workbook but NOT referenced from a cell did not show up for me (not very likely I admit!)

coltaalex: can you copy and paste into your post here one of the references in question? If it includes a file name then it is an external link.

Gavin

 

[coltaalex]

here is one, where "Room by Room" is a sheet in my workbook


='Room-by-Room'!GN10002

So this is considered external link ? or external data ?

 

[SkipVought]

"Room by Room" is a sheet in my workbook

Could that be any clearer?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[coltaalex]

Room By Room" is the name is worksheet in my workbook,
here are other references:

=Quotation!D4
='Room-by-Room'!CD10002+'Room-by-Room'!BW10002
='Proposal Input'!L4

where: Quotation, Proposal Input, are also names of the worksheets in my workbook,

 

[SkipVought]

None are EXTERNAL, are they?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[coltaalex]

correct, none of them are external