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

Isn't print_area a string property of the worksheet's pageSetUp? Not sure about print_title but printTitleColumns and printTitleRows are also a property of pageSetUp.

--

Joe

That is what I thought, but either I was doing something wrong or it would not change.

My code went something like this:

dim nme as Name

for each nme in Activeworkbook.Names

    if instr(1,nme.name, ".xls]") <> 0 then

        nme.delete

        ' OR nme.RefersTo = "= A1:A1"
        ' OR nme.RefersTo = ""


    end if

next nme

None of these would take the reference to an external workbook out of the intrinsic names "Page_Area" or "Page_Title". Also, not knowing what worksheet these were on, I manually went through each page looking at the Page Setup dialog to ensure that there wasn't any occurrence of an external reference. However, when I went to "Edit\Links", it was NOT grayed out (that was after removing all cell references.

Any ideas what to try next?

Thanks!


--- Tom

I would suggest that when you loop through the names testing you display the name of the anme and its refersTo property. Examining these might shed some light on the matter.

--

Joe

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.

Sub RemoveLinks()
Dim Link As Variant
    For Each Link In ActiveWorkbook.LinkSources
        ActiveWorkbook.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
    Next
'*** Delete the following lines if you don't want to delete
'*** PrintArea and PrintTitleRows
    ActiveSheet.PageSetup.PrintArea = ""
    ActiveSheet.PageSetup.PrintTitleRows = ""

End Sub

This looks good, Vemaju!

It looks like I need to familiarize myself with the "LinkSources" object. That is one I did not know. Many thanks!


--- Tom

Vemaju,




--- Tom

Vemaju,

Sorry ... the last reply was a false start ... I hit Forward by mistake.

I have a question on your code:

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

I am working with Office XP. At work I only have Office 2000. Is LinkSources in Excel 2000? Does the "BreakLink" method work in Excel 2000?

In XP I tried your routine and it did not work. Then I ran the following routine to get rid of external links in Names:
---------------------------------------------------------------
Public Function Remove_External_Names()

Dim nme As Name

    For Each nme In ActiveWorkbook.Names

        Debug.Print nme.Name, nme.RefersTo

        If InStr(1, nme.RefersTo, ".xls]") <> 0 Then
            nme.Delete
        End If
    Next

End Function
------------------------------------------------------
After I ran this procedure, I ran your procedure with the following modifications and IT WORKED! Any idea what was going on?
------------------------------------------------------

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 "Link " & i & ":" & Chr(13) & var_Array(i)

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

End Function




--- Tom

Hi Tom,

I think you have external links in names. That's why the code didn't remove those links.

I didn't find BreakLink method in XL2000. I'm sure ther is a way to remove those link anyhow.

-vemaju

Hi

Try this one in xl2000

Sub Remove_External_Links_in_Cells()

Dim LinkCell
Dim FormulaCells As Range
Dim SheetsInWorkbook As Object

For Each SheetsInWorkbook In Sheets
    SheetsInWorkbook.Activate
    '*** Error trapping if no formulacells found
    On Error Resume Next
    '*** Select only formula cells
    Set FormulaCells = Cells.SpecialCells(xlFormulas)
    '*** Loop every formulacell in sheet
    For Each LinkCell In FormulaCells
        '*** If you want paste linked value as "normal value"
        If InStr(1, LinkCell.Value, ".xls]") = 0 Then
            LinkCell.Value = LinkCell
        End If
    Next
Next
End Sub


-vemaju