Can I break a link, using a VBA

[russelldt]

I have a master excel file, that populates data (links) to excel files, when they are opened. I want this to be a once off link, so that when I save the new excel file for the first time, all links are removed, just the raw data remains. I know there is a manual option to break the link, but I am hoping for a fail safe automatic solution.

Thanks

 

[RoryA]

You can use code like this:

Sub BreakLinks()
    Dim LinkArray
    Dim i                As Long
    LinkArray = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(LinkArray) Then
        For i = LBound(LinkArray) To UBound(LinkArray)
            ActiveWorkbook.BreakLink LinkArray(i), xlLinkTypeExcelLinks
        Next i
    End If
End Sub

 

[russelldt]

You can use code like this:

Sub BreakLinks()
    Dim LinkArray
    Dim i                As Long
    LinkArray = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(LinkArray) Then
        For i = LBound(LinkArray) To UBound(LinkArray)
            ActiveWorkbook.BreakLink LinkArray(i), xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Thanks for the prompt response, Rory.
This works great as a Macro, but I have to create a Button and run the Macro for it to work.

Is it possible to run this automatically when I Save and exit the workbook?

Thanks

 

[RoryA]

Call it from the Workbook_BeforeSave event.

 

[russelldt]

Call it from the Workbook_BeforeSave event.

Thank you

 

[russelldt]

Thank you

I need to re think this, apologies.,

I want the links to break when I open the workbook, not when I close it. I have addd the Workbook_Open name, but this is not working.

Private Sub Workbook_Open()
BreakLinks
End Sub

Sub BreakLinks()
Dim LinkArray
Dim i As Long

LinkArray = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(LinkArray) Then
For i = LBound(LinkArray) To UBound(LinkArray)
ActiveWorkbook.BreakLink LinkArray(i), xlLinkTypeExcelLinks
Next i
End If
End Sub

 

[RoryA]

Replace activeworkbook with thisworkbook