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

 

[SkipVought]

Hi,

Check Excel Help on External Link

There is a topic; Break a link to an external reference

No VBA required.

Skip,

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

 

[coltaalex]

thank you Skip i fount that, but is not for excel 2007, do you think it will work here ?

 

[SkipVought]

but is not for excel 2007

Well did you look in Excel 2007 help?


Skip,

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

 

[coltaalex]

I'm looking right now, yes i found it in excel 2007, i will try to see how it works

 

[coltaalex]

is not detecting any links,
the "edit links" button is not active.

 

[SkipVought]

Then you do not have any external references.

Skip,

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

 

[coltaalex]

thank you very much Skip

 

[SkipVought]

What lead you to believe that you had external reference?

Skip,

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

 

[coltaalex]

my boss said to delete them,
but first of all i couldn't find them

he told me that he saw some, but he didn't show them to me,
so now i'm trying to find and delete them, because when I'm running the above code it deleting all the formulas also,

 

[SkipVought]

Bosses are notorious for being Excel dunces. ;-)

Skip,

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

 

[Gavona]

Agree with Skip but Hyperlinks or named ranges referring to external files might not show up using that method.

Gavin

 

[SkipVought]

Hyperlinks, I agree.

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

Skip,

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

 

[coltaalex]

so how to delete the Hyperlinks then ?
thank you !!

 

[SkipVought]

  dim ws as worksheet, hl as hyperlink
  for each ws in worksheets
    for each hl in ws.hyperlinks
      hl.delete
    next
  next

Skip,

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

 

[coltaalex]

Hi Skip, look , i run the above code (thank you), but still when i try to save my workbook as Excel Macro_Enabled Template, it giving me the flowing message :

This workbook contains external data.


how can delete this external data? where i can find these external data and delete them. ?

Thank you Skip

 

[SkipVought]

Does your template have imports of external data?

Skip,

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

 

[SkipVought]

What HELP topics have you researched in Excel and Excel VBA HELP?

What issues have you solved or ruled out by analysis?

Which ones remain?

Skip,

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

 

[coltaalex]

well i try to find them , but i can't,
in all places where i looked no links,
but when I'm saving, it saying that i have . so I'm confused

 

[SkipVought]

well i try to find them , but i can't,

try WHAT? Your statement provides absolutely no insight to your dilema!

in all places where i looked no links,

What places?

You never answered my previous post. PLEASE DO.

Skip,

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