Microsoft KB Archive/126093

= Macros to Delete Formula Links =

Article ID: 126093

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q126093



For a Microsoft Excel 98 and later version of this article, see 192895.



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

while a Microsoft Excel 4.0 link may be similar to the following:

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

Note also that 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.

If You Are Using Microsoft Excel 97 for Windows
If you are using Microsoft Excel 97, you can use the Delete Links Wizard to remove links in your workbook.

For additional information about the Delete Links Wizard, please see the following article in the Microsoft Knowledge Base:

188449 : XL97: Delete Links Wizard Available

Visual Basic Code Example (Microsoft Excel 5.0 or later)
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.

Code Example
  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  Run the Should_Delete macro. 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
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.

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

NOTE: The fifth line of this macro is a continuation of the fourth line.

<div class="references_section">