You are currently offline, waiting for your internet to reconnect

Macros to delete formula links in Excel for Mac

This article was previously published under Q192895
For a Microsoft Excel 97 and earlier version of this article, see 126093.
When you open a workbook that contains links to another workbook, MicrosoftExcel asks you if you want to update links. If the file that the linkis referring to no longer exists, or if it has been moved to a differentfolder, you may want to delete the links to avoid this message.
One of the most common link types is a formula link. A link formula in acell can refer to a cell on a closed workbook file. If that file no longerexists, the formula is no longer valid. To delete such a link, click Find on the Edit menu, and search for an (!) exclamation point. Or, if this action fails, search for a ([ ]) square bracket. This will show each link in a sheet. You can then go to each cell, delete the formula, and replace it with the value that was in the cell. If many cells contain links, deleting the links may take some time. To speed up the deletion process, you may want to use one of the following methods. The macros will enable you to delete some links and not others. It does not delete links to workbooks that are open or within the active workbook.

NOTE: These macros may not work if the source file for a link is located on a network drive. If this is the case, the link will be found but not deleted.

Visual Basic Code Example

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
  1. Type the following macro code in a new module sheet:
        Option Base 1    'This macro deletes all formula links in a workbook.    '    'This macro does not delete a worksheet formula that references an open    'book, for example:    '    '   =[Book1.xls]Sheet1!$A$1    '    ' To delete only the links in the active sheet, see the comments    ' provided in the Delete_It macro later in this article.    Public Times As Integer    Public Link_Array As Variant    Sub Should_Delete()    Items = 0 'initialize these names    Times = 0    Link_Array = ActiveWorkbook.LinkSources 'find all document links    Items = UBound(Link_Array) 'count the number of links    For Times = 1 To Items    'Ask whether to delete each link    Msg = "Do you want to delete this link:  " & Link_Array(Times)      Style = vbYesNoCancel + vbQuestion + vbDefaultButton2      Response = MsgBox(Msg, Style)      If Response = vbYes Then Delete_It      If Response = vbCancel Then Times = Items    Next Times    End Sub    Sub Delete_It()    Count = Len(Link_Array(Times))    For Find_Bracket = 1 To Count - 1      If Mid(Link_Array(Times), Count - Find_Bracket, 1) = ":" _      Then Exit For    Next Find_Bracket    'Add brackets around the file name.    With_Brackets = Left(Link_Array(Times), Count - Find_Bracket) & _      "[" & Right(Link_Array(Times), Find_Bracket) & "]"    'Does the replace.    'If you want to remove links only on the active sheet, change the    'next two lines into comments by placing an (') apostrophe in front of    'them as well as the line, "Next Sheet_Select", that closes the loop.    For Each Sheet_Select In ActiveWorkbook.Worksheets    Sheet_Select.Activate    Set Found_Link = Cells.Find(what:=With_Brackets, After:=ActiveCell, _            lookin:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _            searchdirection:=xlNext, matchcase:=False)       While UCase(TypeName(Found_Link)) <> UCase("Nothing")           Found_Link.Activate           On Error GoTo anarray           Found_Link.Formula = Found_Link.Value           Set Found_Link = Cells.FindNext(After:=ActiveCell)       Wend    Next Sheet_Select 'To remove links only on the active sheet                     'place an (') apostrophe at the front of this line.    Exit Sub    anarray:    Selection.CurrentArray.Select    Selection.Copy    Selection.PasteSpecial Paste:=xlValues    Resume Next    End Sub					
  2. Run the Should_Delete macro.
  3. Each link in the workbook is identified, and for each link you are asked whether you want to delete the link. If you click Yes, the link is deleted, and the current cell value is saved in each of the cells that were previously linked.
MacXLX XL2001 XL98

Article ID: 192895 - Last Review: 10/06/2011 05:08:00 - Revision: 5.0

  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
  • kbdtacode kbhowto KB192895