Macros to Delete Formula Links >> Remove versus paste as value
Greg Albertini
Can someone please shed some light for me?
I have found the code below to search my worksheet and remove all
links to external files. The problem is that the code will past the
formula as a value, I need to remove the link and leave the formula as
is. I can identify the line that does the value past but the code is
above my head to understand how to remove the link versus do the past.
btw - I need to remove via code so the link removal is hidden to the
end user.
Any help will be a BIG BIG help.
Thanks in advance.
Greg
>>> 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.
Shah Shailesh
Try this,
Replace this code in Sub Delete_It
Found_Link.Formula = Found_Link.Value
with this code.
Msg = "Do you want to delete formula also?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
response = MsgBox(Msg, Style)
If response = vbYes Then
found_link.Formula = found_link.Value
Else
new_formula = Application.Substitute
(found_link.Formula, with_brackets, "")
found_link.Formula = new_formula
End If
So when you press no to this response it will remove only
external link & keep your formula as it is. Please note
that if same sheet name is not found then excel will
prompt for file not found dilogbox & if pressing Cancel to
this prompt will put #REF! in Cell.
Regards,
Shah Shailesh
BrianB
is. >>
A contradiction in terms because the links are contained in the
formulas (and other places).
1. Perhaps instead of pasting the value you could find a way to put a
formula to refer to somewhere else on the worksheet.
2. There may be a way to stop the update/message via workbook_open()
event.
3. You can stop links updating if you open the workbook via code.
REgards
BrianB
================================================
greg.al...@cox.net (Greg Albertini) wrote in message news:<2aa3d28e.02090...@posting.google.com>...
Tom Ogilvy
point at the existing workbook rather than the foreign workbook. If so and
the formulas would be legal references, then you can achieve this by going
to
Edit=>Update Links and select change source - then select the current
workbook as the source.
If this does not work, then it means the current workbook does not have a
structrue identical to the ones refered to in the links - thus you would not
be able to fix them with code unless you had some type of remapping
information in your code.
Regards,
Tom Ogilvy
Greg Albertini <greg.al...@cox.net> wrote in message
news:2aa3d28e.02090...@posting.google.com...
Greg Albertini
THANK YOU!
That work perfect!
"Shah Shailesh" <shahsh...@hotmail.com> wrote in message news:<b09d01c25260$58622400$3aef2ecf@TKMSFTNGXA09>...
Greg Albertini
Yes, my structures are the same. I send out updates of the worksheet
to my users on occasion. I have written an upgrade routine that pulls
over the data from old to new version. The upgrade routine creates
the links to the old version. I wanted to remove the links with code
to make it transparent to my users. The other option would be to
spend a ton of time making my upgrade routine target the data better.
I assume I could automate your solution, but my users change file
names and location and that type of code is beyond me.
Shah's solution work well on the code I posted. I just remed out the
sections that asked for user input. The code now runs and removes all
links, leaving the formulas as I need them.
Thanks to all that posted replies.
These newsgroups are an awsome resource and the posters make it all
happen!!!
"Tom Ogilvy" <twog...@msn.com> wrote in message news:<OGKXp5oUCHA.1644@tkmsftngp08>...
> The only logical interpretation of your request is that the formulas should
> point at the existing workbook rather than the foreign workbook. If so and
> the formulas would be legal references, then you can achieve this by going
> to
>
> Edit=>Update Links and select change source - then select the current
> workbook as the source.
>
> If this does not work, then it means the current workbook does not have a
> structrue identical to the ones refered to in the links - thus you would not
> be able to fix them with code unless you had some type of remapping
> information in your code.
>
> Regards,
> Tom Ogilvy
------snip----
Tom Ogilvy
to text string using replace
for example, replace all equal signs (=) with ZZZ=
do the copy of the sheets
No do a replace to replace all the ZZZ= with an equal sign in the new
sheets.
Using the Cells.Replace command does this rapidly in with one command.
Regards,
Tom Ogilvy
"Greg Albertini" <greg.al...@cox.net> wrote in message
news:2aa3d28e.0209...@posting.google.com...
Greg Albertini
Almost seems to easy. The simple solutions are the most elegant and
sometimes the hardest to realize.
Thanks Tom.
"Tom Ogilvy" <twog...@msn.com> wrote in message news:<eOMwPr0UCHA.2612@tkmsftngp13>...