Removing external links from Excel

[TomCarnahan]

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

 

[TonyJollans]

Hi Tom,

Why not just ignore them? Does it make sense (is it even possible?) to have external references in either of these ranges? That said, if you want to change them you can use the underlying data instead ..

[blue]

With .PageSetup
    .PrintArea = "A1:F50"

[green]

' or whatever

[/green]

    .PrintTitleRows = "$1:$2"

[green]

' or whatever

[/green]

    .PrintTitleColumns = "$A:$B"

[green]

' or whatever

[/green]

End With

[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[TomCarnahan]

I would like to ignore them but the people I support would rather have them ALL gone if we go through the process of removing all of them in the cells and names.

I tried:

for each nme in Activeworkbook.Names

if instr(nme.name, &quot;.xls]&quot <> 0 then

nme.delete

'OR nme.RefersTo &quot;&quot;

'OR nme.RefersTo &quot;= A1:A1&quot;

end if

next nme

This worked on all external links in Names except for &quot;Page_Area&quot; and &quot;Page_Title&quot;.

--- Tom

 

[TonyJollans]

Hi Tom,

I really don't think you'll find any external references in these ranges, but I'm confused by your code. You are checking the Name, not the RefersTo. Apart from that why not do as I said, something like ..

[blue]

If nme.Name = "Print_Area" then
    ActiveWorkbook.PageSetup.PrintArea = ""
Else
    nme.Delete
End If

[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[TomCarnahan]

[navy]
Tony,

You were right ... my procedure was at work where I don't have Internet access so I was doing it from memory at home. I meant I was checking the nme.RefersTo property for the occurrence of &quot;.xls]&quot;.

Some background: the reason my users don't like external links is that there is the potential for someone to get in a hurry when they are opening these huge, complicated spreadsheets, and click on &quot;yes&quot; when it asks to refresh external links. They are paranoid that the sheet will get corrupted by someone's inattention, so they would rather not have any external links.


The only problem I can see is that when I cycle through all the Names in my example, it comes up with only one &quot;Print_Area&quot; and &quot;Print_Title&quot;. It does not give any indication as to which worksheet it is referring.

I did write a procedure that cycled through all of the worksheets and printed out its &quot;Print_Area&quot; and &quot;Print_Title&quot; values, but the string containing &quot;.xls]&quot; did not show up. It was only present when I cycled through the Name.RefersTo properties.

After I had 1) removed ALL cell references to other workbooks, 2) removed all Name.RefersTo strings that pointed to an external workbook, 3) tried to delete the &quot;Print_Area&quot; and &quot;Print_Title&quot; names and 4) tried to set their values to &quot;&quot;, when I went out to the user interface and to the menu &quot;Edit\Links&quot;, the dialog box showed I still had active links. Using the 3 buttons in that dialog box did not resolve the issue either.

Just as feedback, another person gave me this possible solution ... I will have to check it out when I get to work. It involves the &quot;LinkSource&quot; object which I was not familiar with:

[/navy]
===================================================
Author: vemaju
Replied on: 02/21/2004 1:47:39 PM
Message:

Hi,
I'm not sure if you want to delete all links and print area and rows to print, but anyway here's the code that removes all external links.

[purple]
Sub RemoveLinks()

Dim Link As Variant

For Each Link In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink Name:=Link, _
Type:=xlLinkTypeExcelLinks
Next Link

'*** Delete the following lines if you
' don't want to delete PrintArea and PrintTitleRows

ActiveSheet.PageSetup.PrintArea = &quot;&quot;

ActiveSheet.PageSetup.PrintTitleRows = &quot;&quot;

End Sub
[/purple]

===================================================


--- Tom

 

[TomCarnahan]

New development:

I tried the following 2 procedure in sequence, first to get rid of references to external workbooks in the Names collection, then to convert the external references in cell formulas to values. From my experimentation, it seems you must do these in order to be successful. If you do them in reverse order, I don't think you will remove all links:
-----------------------------------------------------------

[navy]
Public Function Remove_External_Names()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Run this FIRST to get rid of external names, then run
' &quot;Remove_External_Links_in_Cells&quot; to get rid of the links
' in cells.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim nme As Name

For Each nme In ActiveWorkbook.Names

Debug.Print nme.Name, nme.RefersTo

If InStr(1, nme.RefersTo, &quot;.xls]&quot <> 0 Then
nme.Delete
End If
Next

End Function
[/navy]

[purple]
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function Remove_External_Links_in_Cells()

Dim var_Array As Variant
Dim i As Integer

var_Array = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(var_Array) Then
For i = 1 To UBound(var_Array)

Debug.Print &quot;Link &quot; & i & &quot;:&quot; & Chr(13) & var_Array(i)

ActiveWorkbook.BreakLink _
Name:=var_Array(i), _
Type:=xlExcelLinks
Next i
End If

End Function
-----------------------------------------------------------
[/purple]

I tried the 2 procedures at home using Excel 2002. I need to try it at work where I only have Excel 2000. I still don't know how I got external references in &quot;Print_Area&quot; and &quot;Print_Title&quot;, and I don't know if my process will get rid of them.

What's the verdict?

Any thoughts or similar experiences?

(I'm going to bed ... more later)

Thanks!


--- Tom