Friday, December 08, 2006

Removing External Links in Microsoft Excel SpreadSheet

Removing External Links in Excel SpreadSheet:

Some Microsoft Excel Spreadsheet will have links to other workbooks. If this needs to be broken use the following code:

Sub Remove_External_Links_in_Cells()

Dim LinkCell
Dim FormulaCells As Range
Dim SheetsInWorkbook As Object

For Each SheetsInWorkbook In Sheets
'*** 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"
MsgBox LinkCell.Parent.Name
If InStr(1, LinkCell.Value, ".xls]") = 0 Then
LinkCell.Value = LinkCell
End If
End Sub

search engine submissionscholarships
online degrees, online colleges
scholarship information

online schools and colleges


  1. Anonymous5:15 PM

    Yeah but what if you have a sheet with no formulas? The code crashes.

  2. Anonymous3:30 PM

    This code sucks and doesnt work. Sorry you have failed.

  3. Anonymous7:52 PM

    Try looking for bad named ranges. If you have any named ranges that are pointing to an errant range (particularly located in another workbook), they should be deleted.


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.