XL: Macros to Delete Formula Links

• Microsoft Excel for the Macintosh, version 5.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0

SUMMARY

When you open a workbook that contains links to another workbook, Microsoft Excel asks you if you want to update links. If the file that the link is referring to no longer exists, or if it has been moved to a different folder, you may want to delete the links to avoid this message.

MORE INFORMATION

One of the most common link types is a formula link. A link formula in a cell can refer to a cell on a closed workbook file. If that file no longer exists, the formula is no longer valid. To delete such a link, click Find on the Edit menu, and search for an (!) exclamation point. 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.

NOTE: The Visual Basic Code Example is specifically for Microsoft Excel version 5.0 and later. Also, note that the Microsoft Excel 4.0 example will not work in Microsoft Excel version 5.0 and later because of the difference in the link formulas. For example, a link formula in Microsoft Excel 5.0 and later may be similar to the following

   ='c:\Excel\[book1.xls]sheet1'!$a$1 

   ='c:\Excel\sheet1.xls'!$a$1 

If You Are Using Microsoft Excel 97 for Windows

Visual Basic Code Example (Microsoft Excel 5.0 or later)

Code Example

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
         'Replace the "\" in the next line with a ":" if you are using
         'Microsoft Excel for the Macintosh.
         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.

Microsoft Excel 4.0 Example

Example

 link=LINKS()
 =FOR("counter",1,COLUMNS(link))
 current=INDEX(link,,counter)
 delete=ALERT("To remove the link:  "&current&", press OK. To leave it
     alone press Cancel",1)
 =IF(delete=FALSE,NEXT())
 search="='"&current&"*"
 =ERROR(FALSE)
 =FORMULA.FIND(search,1,2)
 =FORMULA.REPLACE(search,GET.CELL(5),,,TRUE,FALSE)
 test_error=TRUE
 =WHILE(test_error=TRUE)
 next_cell=FORMULA.FIND.NEXT()
 =IF(next_cell=FALSE,SET.NAME("test_error","false"))
 =FORMULA.REPLACE(search,GET.CELL(5),,,TRUE,FALSE)
 =NEXT()
 =ERROR(TRUE)
 =NEXT()
 =RETURN() 

REFERENCES

"Function Reference," version 4.0, page 258-259

For additional information, please see the following articles in the Microsoft Knowledge Base:

Q188449 : XL97: Delete Links Wizard Available on MSL

Additional query words: XL97 macro break links

Keywords          : kbdta kbdtacode xlloadsave xlvbahowto xlformat xlformula 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS 
Issue type        : kbinfo 

Last Reviewed: May 17, 1999