break links not working

[amineact]

Hello there. I am in a bit of a pickle. I'm trying to write a vba code that allows the user to loop through multpile files in a directory and break all their links.
When doing it by hand breaking links seems to work but with vba some files return errors.
How is that possible
Here's the code :

Sub kill_links()
Dim vLinks As Variant
Dim MyPath As String
Dim Myfile As String
Dim wb As Workbook
Dim ncp As Workbook
Dim ws As Worksheet
Dim rg1 As String
Application.ScreenUpdating = False
Set wb = Workbooks("Scén.xlsx")
rg1 = wb.Sheets("Scén").Range("C6").Value
MyPath = "C:\home\myfiles\"
Myfile = Dir(MyPath & "\*.xl*")
Do While Myfile <> ""
   Set ncp = Workbooks.Open(Filename:=MyPath & "\" & Myfile)
     Application.DisplayAlerts = False
     Application.AskToUpdateLinks = False
     ncp.activate
     vLinks = ncp.LinkSources(xlLinkTypeExcelLinks)
     If Not IsEmpty(vLinks) Then
        for i =1 TO Ubound((vLinks)
        wb.BreakLink Name:=vLinks(i), Type:=xlLinkTypeExcelLinks
        Next 
     End If

     ncp.Close SaveChanges:=True
    End If
  
    
 Myfile = Dir
 Loop
     
Application.ScreenUpdating = True

End Sub

Thank you

 

[Max1616]

I made some slight changes to your code (I didn't test it, so hopefully it works).

Sub kill_links()
Dim vLinks As Variant
Dim MyPath As String
Dim Myfile As String
Dim wb As Workbook
Dim ncp As Workbook
Dim ws As Worksheet
Dim rg1 As String
Application.ScreenUpdating = False
Set wb = Workbooks("Scén.xlsx")
rg1 = wb.Sheets("Scén").Range("C6").Value
MyPath = "C:\home\myfiles\"
Myfile = Dir(MyPath & "\*.xl*")
Do While Myfile <> ""
   Set ncp = Workbooks.Open(Filename:=MyPath & "\" & Myfile)
     Application.DisplayAlerts = False
     Application.AskToUpdateLinks = False
     ncp.Activate
     vLinks = ncp.LinkSources(xlLinkTypeExcelLinks)
     If Not IsEmpty(vLinks) Then
        For i = 1 To UBound(vLinks)
        ncp.BreakLink Name:=vLinks(i), Type:=xlLinkTypeExcelLinks
        Next
     End If

     ncp.Close SaveChanges:=True
    End If
  
    
 Myfile = Dir
 Loop
     
Application.ScreenUpdating = True

End Sub

Changes made:

1. Deleted an extra ")" after ubound(vlinks)
2. Changed the workbook the breaklink method was pointing towards (from wb to ncp)

 

[amineact]

I made some slight changes to your code (I didn't test it, so hopefully it works).

Sub kill_links()
Dim vLinks As Variant
Dim MyPath As String
Dim Myfile As String
Dim wb As Workbook
Dim ncp As Workbook
Dim ws As Worksheet
Dim rg1 As String
Application.ScreenUpdating = False
Set wb = Workbooks("Scén.xlsx")
rg1 = wb.Sheets("Scén").Range("C6").Value
MyPath = "C:\home\myfiles\"
Myfile = Dir(MyPath & "\*.xl*")
Do While Myfile <> ""
   Set ncp = Workbooks.Open(Filename:=MyPath & "\" & Myfile)
     Application.DisplayAlerts = False
     Application.AskToUpdateLinks = False
     ncp.Activate
     vLinks = ncp.LinkSources(xlLinkTypeExcelLinks)
     If Not IsEmpty(vLinks) Then
        For i = 1 To UBound(vLinks)
        ncp.BreakLink Name:=vLinks(i), Type:=xlLinkTypeExcelLinks
        Next
     End If

     ncp.Close SaveChanges:=True
    End If
 
   
 Myfile = Dir
 Loop
    
Application.ScreenUpdating = True

End Sub

Changes made:

1. Deleted an extra ")" after ubound(vlinks)
2. Changed the workbook the breaklink method was pointing towards (from wb to ncp)

Thanks for the reply, im afraid i made some errors in my original code
However, the problem still persists. For some workbooks it does a perfect job breaking the links however for the other i get the error Method 'Break-Link' of object '_Workbook' failed".
I appreciate the help

 

[Max1616]

Hmmm, I'm unsure. It seems like this should work for all workbooks. Perhaps the sheets/cells that you're trying to break the links on is protected. You could add the below so it skips any workbooks that throw an error:

On Error Resume Next