VBA Copy Sheets and Break All External Links

[cheongmarcus]

Hi, I am new to VBA and would really appreciate if anyone could help me with this. Currently, I am trying to only copy certain sheets out of a workbook and save it in new workbook. However, in my new workbook with those selected worksheets, when i go to Edit Links, there is link to my previous workbook. Is it possible to break this link? I would like to incorporate this in the VBA code, so then I will not need to open up this new workbook and manually go and break link.

I have this code to copy certain worksheets:

Sub Seperate_Sheets()

Dim Path1 As String
Path1 = ActiveWorkbook.Path & "\" & "Current Budget "

Sheets(Array("Expenses", "Revenues", "Previous Year Expenses", "Previous Year Revenue")).Copy
ActiveWorkbook.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

End Sub

However, I will want to break all links in this new workbook ("Current Budget"). What code and how can it be inserted inside this current code I am using?

Thank you! Any help will be very much appreciated!

 

[dmt32]

Hi welcome to forum

try following update to your code

Sub Seperate_Sheets()
    Dim Path1 As String
    Dim wb As Workbook
    
    Path1 = ActiveWorkbook.Path & "\" & "Current Budget"
    
    Sheets(Array("Expenses", "Revenues", "Previous Year Expenses", "Previous Year Revenue")).Copy
    
    Set wb = ActiveWorkbook
    
    BreakAllLinks wb
    
    wb.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    wb.Close False
    
End Sub

Following sub should be placed in a STANDARD module

Sub BreakAllLinks(ByVal wb As Object)
    Dim Link As Variant, LinkType As Variant
    
    For Each LinkType In Array(xlLinkTypeExcelLinks, xlOLELinks, xlPublishers, xlSubscribers)
        If Not IsEmpty(wb.LinkSources(Type:=LinkType)) Then
            For Each Link In wb.LinkSources(Type:=LinkType)
                wb.BreakLink Name:=Link, Type:=LinkType
            Next Link
        End If
    Next LinkType
    wb.UpdateLinks = xlUpdateLinksNever
End Sub

Dave

 

[cheongmarcus]

Hi Dave, thank you for the help! May I know what is a standard module? Also, is this standard module to be placed right below my updated code like this (in picture)? Because i tried running it, but the link still did not break. Did I do anything wrong?

 

[cheongmarcus]

Or where can i insert this standard module at?

 

[dmt32]

Hi,
a standard module is from the VB editor Insert > Module.

If you have the code is same place as your Seperate_Sheets code then this should ok & remove links.

Dave

 

[cheongmarcus]

can I put both my Separate_Sheets code and this BreakAllLinks code under the same module? like Insert > Module and then paste it there?

Marcus

 

[dmt32]

can I put both my Separate_Sheets code and this BreakAllLinks code under the same module? like Insert > Module and then paste it there?

Marcus

that should be ok

Dave

 

[RustEE2020]

Hi welcome to forum

try following update to your code

Sub Seperate_Sheets()
    Dim Path1 As String
    Dim wb As Workbook
   
    Path1 = ActiveWorkbook.Path & "\" & "Current Budget"
   
    Sheets(Array("Expenses", "Revenues", "Previous Year Expenses", "Previous Year Revenue")).Copy
   
    Set wb = ActiveWorkbook
   
    BreakAllLinks wb
   
    wb.SaveAs Filename:=Path1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    wb.Close False
   
End Sub

Following sub should be placed in a STANDARD module

Sub BreakAllLinks(ByVal wb As Object)
    Dim Link As Variant, LinkType As Variant
   
    For Each LinkType In Array(xlLinkTypeExcelLinks, xlOLELinks, xlPublishers, xlSubscribers)
        If Not IsEmpty(wb.LinkSources(Type:=LinkType)) Then
            For Each Link In wb.LinkSources(Type:=LinkType)
                wb.BreakLink Name:=Link, Type:=LinkType
            Next Link
        End If
    Next LinkType
    wb.UpdateLinks = xlUpdateLinksNever
End Sub

Dave

I tried using your BreakAllLinks code and I still have external links pointing to another workbook after running it. These links are pointing at an .xlam that I am using to copy worksheets to another workbook I am then running your code on that workbook. The links that are not being broken are part of a Vlookup, would this cause this code not to work?

 

[dmt32]

Difficult to say - can you place copy of your workbook with dummy data in on a filesharing site like dropbox & provide a link to it.

Dave

 

[RustEE2020]

I would have to make a different workbook to do that, as it is a work related workbook.
The solution I came up with is to first run a loop that replaces all the instances of an external link name with the name of the workbook they now reside in.
Excel recognizes this as a relative link and removes the callout for the excel sheet from the visible formula.
In the same loop after the replace mentioned above if the replace function could not be applied I copy the data to a variant, clear the formula and recopy the data back to the cell.
So far it seems to be working fine, links that can be made relative inside the workbook are and those that aren't are replaced with their last known value.