Removing external links in Excel

In my group at work, we have a lot of problems with "links" (external

references to other workbooks) showing up in our Excel workbooks. Part

of this is our analysts propensity to use old workbooks as "templates"

for new projects.

To remove the links, I wrote a procedure that cycled through all the

worksheets to show me which worksheets contained cells with the string

".xls]" in their formulas (one form of external link). That allowed me

to perform a "find and replace" to remove the offending string in the

formulas.

Since these external links also appear in Excel Name objects, I wrote

another procedure that cycles through all Names and allows the user to

selectively delete any the Name object that refers to external

workbooks.

This routine worked okay except for two cases: the intrinsic Names

"Print_Area" and "Print_Title" . I was not able to delete or change

their "RefersTo" property to remove the external reference.

Would any one have experience using VBA to remove the external

references in those two intrinsic Excel Name objects?

Note: I know there are some utilities that do all of the above via

automation, but I cannot use them in my work environment due to LAN

restrictions, so just being able to write a VBA procedure that deals

with these two intrinsic names is all I really need.

Thank you ahead of time for your assistance!

--- Tom

Tom Carnahan wrote:
Note: I know there are some utilities that do all of the above via
automation, but I cannot use them in my work environment due to LAN
restrictions


Pity.
FINDLINK.XLA from http://www.BMSLtd.ie/mvp would do this for you.

I think you will find that PrintArea and PrintTitles are
worksheet-level names.

Here is a relevant bit of code from FINDLINK:

If Right(obj.Name, 13) = "!Print_Titles" Then
' cannot delete this name but can kill it via PageSetup
With Sheets(Left(obj.Name, Len(obj.Name) - 13)).PageSetup
.PrintTitleColumns = ""
.PrintTitleRows = ""
End With
End If

where obj is the Name object. Hope it helps.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup