Finding formulas with links

[Jimtwilaho]

I work in an office which is using excel files with numerous links. We often
share the files back and forth, but because each person's directory is
different, the links can't always find the file paths. So, we have to edit
links or change the formulas. I would like to be able to loop through
worksheets to locate cells that have specified links in them, rather than
checking each cell. The files can be large, so just displaying the formulas
and looking is still quite tedious. Does anyone know of VBA code that will work
for this?

[Giuseppe Latorraca]

Hi Jimtwilaho,
I simply attach the Q126093 article coming from Microsoft with (I hope) the
solution of your problem. Note that in text there is also a reference to the
Q188449 article, about a "Delete Links Wizard" that I haven't tested. It could be a
great idea to distribute that add-in, instead of develop and mantain a vba code.

Best regards,
Giuseppe

>>> XL: Macros to Delete Formula Links <<<

PSS ID Number: Q126093
Article last modified on 05-17-1999

WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0

MACINTOSH WINDOWS

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

- 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

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:

Q188449 : XL97: Delete Links Wizard Available on MSL

Visual Basic Code Example (Microsoft Excel 5.0 or later)
--------------------------------------------------------

Microsoft provides programming examples for illustration only, without warranty
either expressed or implied, including, but not limited to, the implied
warranties of merchantability and/or fitness for a particular purpose. This
article assumes that you are familiar with the programming language being
demonstrated and the tools used to create and debug procedures. Microsoft
support professionals 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 needs. If you have
limited programming experience, you may want to contact the Microsoft fee-based
consulting line at (800) 936-5200. For more information about the support
options available from Microsoft, please see the following page on the World
Wide Web:

http://www.microsoft.com/support/supportnet/refguide/

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
---------------------------

Microsoft provides programming examples for illustration only, without warranty
either expressed or implied, including, but not limited to, the implied
warranties of merchantability and/or fitness for a particular purpose. This
article assumes that you are familiar with the programming language being
demonstrated and the tools used to create and debug procedures. Microsoft
support professionals 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 needs. If you have
limited programming experience, you may want to contact the Microsoft fee-based
consulting line at (800) 936-5200. For more information about the support
options available from Microsoft, please see the following page on the World
Wide Web:

http://www.microsoft.com/support/supportnet/refguide/

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.

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
=============================================================================
Copyright Microsoft Corporation 1999.

[Jimtwilaho]

Guiseppe,

Exactly what I was looking for! Thanks so much.

Jim