'438: Object Does not Support' error with breaking links via VBA

BLaCKouT

New Member
Joined
May 31, 2009
Messages
42
Afternoon folks,
I've got a list of files in Column A of a sheet (path and filename), with a modified-date in column B and file-size in column C.
For each item in the list, I want to open the file in Column A, break the links to other workbooks, save and close the file, then return the new file size in Column E.
(please forgive my shoddy coding)
Code:
Sub FileListFebruaryRun()
Dim rowx As Long
Dim TargetFile As String
rowx = 3
If Range("A" & rowx) = "" Then
MsgBox ("You've got to give me 'something' to work with!")
Exit Sub
Else
'
Do
TargetFile = Range("A" & rowx).Value
Workbooks.Open Filename:=TargetFile, UpdateLinks:=xlUpdateLinksNever
''
    Dim Linksidontwant As Variant
    Dim x As Integer
    With ActiveWorkbook
    Linksidontwant = .LinkSources(xlLinkTypeExcelLinks)
    If Not IsEmpty(Linksidontwant) Then
    For x = 1 To UBound(Linksidontwant)
    ActiveWorkbook.BreakLinks (x), xlLinkTypeExcelLinks
    Next x
    End If
    End With
    ActiveWorkbook.Save
    ActiveWorkbook.Close
''
Workbooks("File List Retriever.xls").Activate
Worksheets("February Files to Update").Activate
Range("A" & rowx).Select
ActiveCell.Offset(0, 4) = FileLen(ActiveCell)
rowx = rowx + 1
Loop Until IsEmpty(ActiveCell.Offset(1, 0))
''
End If
End Sub
The routine runs perfectly for the first five lines, then it pauses for a second and gives me
Run-time error '438':
Object doesn't support this property or method
When I hit Debug it highlights this line:
Code:
ActiveWorkbook.BreakLinks (x), xlLinkTypeExcelLinks
My coding aside, I can't work out why the method would work for five lines and then trigger an error.
Am I using completely the wrong command for breaking the links?
Many thanks in advance,
B.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
It's BreakLink not BreakLinks.

Thank you, Andrew. Although when I run it with that change, it stops in at the same file and gives me:

Run-time error '1004':
Method 'Breaklink' of object '_Workbook' failed


and highlights the line
Code:
ActiveWorkbook.BreakLink (x), xlLinkTypeExcelLinks
 
Upvote 0
Code:
    For x = 1 To UBound(Linksidontwant)
    ActiveWorkbook.BreakLink Linksidontwant(x), xlLinkTypeExcelLinks
    Next x
 
Upvote 0
Code:
    For x = 1 To UBound(Linksidontwant)
    ActiveWorkbook.BreakLink Linksidontwant(x), xlLinkTypeExcelLinks
    Next x
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top