External links

[Pinter]

Im having a problem with some external links. My file says there are links to other files, but nowhere in the current file is there any reference to them. How is this possible? And how do I delete the links? Ive tried changing the source and that doesnt work. Thanks in advance.

[Barrie Davidson]

On 2002-05-14 08:09, Pinter wrote:
Im having a problem with some external links. My file says there are links to other files, but nowhere in the current file is there any reference to them. How is this possible? And how do I delete the links? Ive tried changing the source and that doesnt work. Thanks in advance.

Pinter, have a look at:

http://support.microsoft.com/default...;en-us;Q188449

HTH

Regards,

[Ricky Morris]

Check the range names in your workbook if they are linked to other files.

[NateO]

The name ranges and charts get 'em every time. Good link Barrie, it doesn't sound like the file prompts the user for a delete, have to have a look see.

I'll post this for the halibut, had to adjust the hyperlinking to accomodate xl97:

Sub Links()

Dim ws As Worksheet, i As Integer, cl As Range, frm As String

Dim rng As Range, n As Integer, FrmCls As Range

Application.ScreenUpdating = False

On Error GoTo errorhandler

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "SearchResults"

redo:

Sheets("SearchResults").[a1] = "Results:"

n = Sheets.Count

For Each ws In ActiveWorkbook.Worksheets

    If ws.Name = "SearchResults" Then GoTo 2

    On Error Resume Next

    Set FrmCls = ws.UsedRange.SpecialCells(xlFormulas)

    If FrmCls Is Nothing Then GoTo 1

    For Each cl In FrmCls

        frm = cl.Formula

        If InStr(frm, "[") Then

            Set rng = cl

            Sheets(n).[a65536].End(xlUp).Offset(1) = "'" & frm

            On Error GoTo 0

            Sheets(n).[a65536].End(xlUp).Offset(, 1) = _

             ws.Name & "!" & rng.Address(False, False)

            Sheets(n).Hyperlinks.Add _

             Anchor:=Sheets(n).[a65536].End(xlUp).Offset(, 1), _

             Address:="", _

             SubAddress:=ws.Name & "!" & rng.Address

        End If

    Next cl

1:

    Set FrmCls = Nothing

    Next ws

2:

Sheets("SearchResults").[a:b].EntireColumn.AutoFit

Application.ScreenUpdating = True

End

errorhandler:

Application.DisplayAlerts = False

Sheets(Sheets.Count).Delete

Application.DisplayAlerts = True

Application.ScreenUpdating = True

Select Case MsgBox("You need to delete the results sheet before " & _

    "running this procedure." & Chr(13) & Chr(13) & _

    "Click 'Yes' to refresh the results sheet, click" & _

    " 'NO' to cancel this procedure", vbYesNo)

Case vbYes

    Application.DisplayAlerts = False

    Sheets("SearchResults").Delete

    Application.DisplayAlerts = True

    Links

Case vbNo

    Application.ScreenUpdating = True

End Select

End Sub

[Pinter]

That add-in works great. Thanks for the help.

[wirelessengineer]

I tried the add-in from the MS website.

Still got the unwanted link!

When I go to Edit -> Links, I see the link. The Item column is blank. Type is Worksheet, Status is A.

I also tried your macro. Zip.

Any ideas?

[ This Message was edited by: wirelessengineer on 2002-06-12 11:58 ]

[wirelessengineer]

SOLVED!

The order appears to be essential here, because I had tried these steps in other combinations with no success.

First I went to Tools -> Options. On the "Calculations" tab I unchecked 'Update remote references' AND 'Save external link values'. Then click OK.

Then I went to Edit -> links, and clicked on 'Change Source'. I changed the source to point back to the file I'm using, then saved the file.

Done!


Yet another kludgy workaround to an MS stupidity!

How hard would it be to have a "Delete Link" button on the Edit -> Links box?

Apparently too hard for MS. =



[ This Message was edited by: wirelessengineer on 2002-06-12 14:13 ]