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)
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:
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.
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
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
Am I using completely the wrong command for breaking the links?
Many thanks in advance,
B.