asked on

Excel Links

I have an excel macro-enabled workbook, call it "seating plans", that refers to a data table in a separate workbook. It does so both through pivot tables and in formulas.

Sometimes it asks to update links when I open it. Sometimes it doesn't. It doesn't seem to matter if the data table worksheet is open or not when opening the seating plans workbook.

Is there any rationale to this?!

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

This will remove all links in the active workbook

Option Explicit

Sub BreakLink()

    Dim aLinks As Variant
    Dim Ctr   As Long

    aLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(aLinks) Then
        For Ctr = LBound(aLinks) To UBound(aLinks)
            ActiveWorkbook.BreakLink Name:=aLinks(Ctr), _
                                     Type:=xlLinkTypeExcelLinks
        Next Ctr
        Else: MsgBox "No links found", vbInformation
    End If

End Sub

Open in new window

Ben Cheetham

ASKER

Hi

Thanks for the answers so far.

I need to keep the links. I am just curious as to why sometimes the workbook asks to update links and very occasionally it doesn't. It doesn't seem to matter whether or not the data workbook is open or not.

It's not causing a major issue so I'll close the question in a day or two if no joy.
Thanks
Ben

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.