Help with "Run-time error '1004': Method 'Break-Link' of object '_Workbook' failed"

[Eraj]

Hi All,

I am working on an Excel file which has 12 sheets in total of which the following 5 sheets only are protected and have links to another excel file. Because of links to another file, the file I am working on is huge in size (about 10 MB).

1. Quote 1
2. Quote 2
3. Quote 3
4. Quote 4
5. Quote 5

So I have written below macro to first unprotect these 5 sheets, then break the links and finally protect the sheets again.

Sub BreakThoseLinks()
Application.ScreenUpdating = False
Dim myLinks As Variant
myLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

Sheets("Quote 1").Unprotect "password"
Sheets("Quote 2").Unprotect "password"
Sheets("Quote 3").Unprotect "password"
Sheets("Quote 4").Unprotect "password"
Sheets("Quote 5").Unprotect "password"

For i = 1 To UBound(myLinks)
ActiveWorkbook.BreakLink Name:=myLinks(i), Type:=xlLinkTypeExcelLinks
Next i

Sheets("Quote 1").Protect "password"
Sheets("Quote 2").Protect "password"
Sheets("Quote 3").Protect "password"
Sheets("Quote 4").Protect "password"
Sheets("Quote 5").Protect "password"

Application.ScreenUpdating = True
End Sub

The macro works fine when I run it in the module using F8, however, when I try to run it by clicking F5 or create a button and assign macro to the button and run it, I encounter following error:

"Run-time error '1004':
Method 'Break-Link' of object '_Workbook' failed"

Help in understanding the reason for the error message and suggestions to fix the same would be highly appreciated!

Thanks,
Eraj.

 

[Kmahraz]

I don't see nothing wrong with your code, it can be an issue with your Trust Center Settings, please try the instructions in the link below and let us know if it solved your problem.

Also, try to load a sample file it help get answers from the NInja's quickly.

http://bajivemula.blogspot.com/2012/12/run-time-error-1004-method-vbproject-of_31.html

 

[Eraj]

I don't see nothing wrong with your code, it can be an issue with your Trust Center Settings, please try the instructions in the link below and let us know if it solved your problem.

Also, try to load a sample file it help get answers from the NInja's quickly.

http://bajivemula.blogspot.com/2012/12/run-time-error-1004-method-vbproject-of_31.html

Thanks, Kmahraz. However, above did not help.

I am unable to attach the file...I get an error message which says the file is too large (it is about 10 mb).

Do I have any other option?

Thanks,
Eraj.

 

[Eraj]

I figured out that using paste special values works and hence am using the same feature instead of break links.

Thanks,
Eraj.

 

[Kmahraz]

Glad you were able to figure out the issue.