Help to Break links please!!
 

Hi All,

My code copies a summary sheet from one workbook into another, and the
copied sheet contains links that I want to get rid of and formulae that I
want to keep. I have tried various ways to get rid of the links, but have
been unsuccessful so far. I get a runtime error 1004 message 'Method
'BreakLink' of object '_Workbook' failed' at the highlighted line of code
with my latest attempt:

Private Sub UpdateQuarters_Click()

'On Error GoTo ErrorHandler
ActiveSheet.Unprotect

Dim linkpath, homefile, masterquarterfile, quartermonth, sumsheet As Variant

linkpath = Application.ActiveWorkbook.FullName
homefile = ActiveWorkbook.Name
sumsheet = ActiveSheet.Name

MsgBox "Please select the Quarterly Template file you wish to open",
vbOKOnly, "Select File"
masterquarterfile = Application.GetOpenFilename()
A = MsgBox("Open " & masterquarterfile & "?", vbYesNoCancel, "Open Quarterly
Template file")
If A = vbNo Then
'GoTo ErrorHandler
Exit Sub
Else
If A = vbCancel Then
'GoTo ErrorHandler
Exit Sub
End If
End If
Workbooks.OpenText masterquarterfile
masterquarterfile = ActiveWorkbook.Name

Windows(homefile).Activate
Sheets(sumsheet).Select
ActiveSheet.Unprotect
linkpath = Application.ActiveWorkbook.FullName
Sheets(sumsheet).Copy After:=Workbooks(masterquarterfile).Sheets("front" )
ActiveSheet.Shapes("UpdateSummary").Select
Selection.Delete
ActiveSheet.Shapes("UpdateQuarters").Select
Selection.Delete

MsgBox linkpath

ActiveWorkbook.BreakLink Name:=linkpath, Type:=xlLinkTypeExcelLinks <=ERROR
LINE

etc etc

Help to Break links please!!
 

You need to specify which of the links you are trying to break if you have
only one, add a number 1 in parenthesis nex to your link name like so:

ActiveWorkbook.BreakLink Name:=linkpath(1), Type:=xlLinkTypeExcelLinks
<=ERROR
LINE


--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"ewan7279" wrote:

Hi All,

My code copies a summary sheet from one workbook into another, and the
copied sheet contains links that I want to get rid of and formulae that I
want to keep. I have tried various ways to get rid of the links, but have
been unsuccessful so far. I get a runtime error 1004 message 'Method
'BreakLink' of object '_Workbook' failed' at the highlighted line of code
with my latest attempt:

Private Sub UpdateQuarters_Click()

'On Error GoTo ErrorHandler
ActiveSheet.Unprotect

Dim linkpath, homefile, masterquarterfile, quartermonth, sumsheet As Variant

linkpath = Application.ActiveWorkbook.FullName
homefile = ActiveWorkbook.Name
sumsheet = ActiveSheet.Name

MsgBox "Please select the Quarterly Template file you wish to open",
vbOKOnly, "Select File"
masterquarterfile = Application.GetOpenFilename()
A = MsgBox("Open " & masterquarterfile & "?", vbYesNoCancel, "Open Quarterly
Template file")
If A = vbNo Then
'GoTo ErrorHandler
Exit Sub
Else
If A = vbCancel Then
'GoTo ErrorHandler
Exit Sub
End If
End If
Workbooks.OpenText masterquarterfile
masterquarterfile = ActiveWorkbook.Name

Windows(homefile).Activate
Sheets(sumsheet).Select
ActiveSheet.Unprotect
linkpath = Application.ActiveWorkbook.FullName
Sheets(sumsheet).Copy After:=Workbooks(masterquarterfile).Sheets("front" )
ActiveSheet.Shapes("UpdateSummary").Select
Selection.Delete
ActiveSheet.Shapes("UpdateQuarters").Select
Selection.Delete

MsgBox linkpath

ActiveWorkbook.BreakLink Name:=linkpath, Type:=xlLinkTypeExcelLinks <=ERROR
LINE

etc etc

Help to Break links please!!
 

Try something like this to break external links in the ActiveWorkbook.

Sub BreakLinks()
Dim Links As Variant
Dim i As Integer

With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End With

End Sub



--
Hope that helps.

Vergel Adriano


"ewan7279" wrote:

Hi All,

My code copies a summary sheet from one workbook into another, and the
copied sheet contains links that I want to get rid of and formulae that I
want to keep. I have tried various ways to get rid of the links, but have
been unsuccessful so far. I get a runtime error 1004 message 'Method
'BreakLink' of object '_Workbook' failed' at the highlighted line of code
with my latest attempt:

Private Sub UpdateQuarters_Click()

'On Error GoTo ErrorHandler
ActiveSheet.Unprotect

Dim linkpath, homefile, masterquarterfile, quartermonth, sumsheet As Variant

linkpath = Application.ActiveWorkbook.FullName
homefile = ActiveWorkbook.Name
sumsheet = ActiveSheet.Name

MsgBox "Please select the Quarterly Template file you wish to open",
vbOKOnly, "Select File"
masterquarterfile = Application.GetOpenFilename()
A = MsgBox("Open " & masterquarterfile & "?", vbYesNoCancel, "Open Quarterly
Template file")
If A = vbNo Then
'GoTo ErrorHandler
Exit Sub
Else
If A = vbCancel Then
'GoTo ErrorHandler
Exit Sub
End If
End If
Workbooks.OpenText masterquarterfile
masterquarterfile = ActiveWorkbook.Name

Windows(homefile).Activate
Sheets(sumsheet).Select
ActiveSheet.Unprotect
linkpath = Application.ActiveWorkbook.FullName
Sheets(sumsheet).Copy After:=Workbooks(masterquarterfile).Sheets("front" )
ActiveSheet.Shapes("UpdateSummary").Select
Selection.Delete
ActiveSheet.Shapes("UpdateQuarters").Select
Selection.Delete

MsgBox linkpath

ActiveWorkbook.BreakLink Name:=linkpath, Type:=xlLinkTypeExcelLinks <=ERROR
LINE

etc etc

Help to Break links please!!
 

Excellent - it works like a dream!!

Thanks Vergel!!

"Vergel Adriano" wrote:


Try something like this to break external links in the ActiveWorkbook.

Sub BreakLinks()
Dim Links As Variant
Dim i As Integer

With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End With

End Sub



--
Hope that helps.

Vergel Adriano


"ewan7279" wrote:

Hi All,

My code copies a summary sheet from one workbook into another, and the
copied sheet contains links that I want to get rid of and formulae that I
want to keep. I have tried various ways to get rid of the links, but have
been unsuccessful so far. I get a runtime error 1004 message 'Method
'BreakLink' of object '_Workbook' failed' at the highlighted line of code
with my latest attempt:

Private Sub UpdateQuarters_Click()

'On Error GoTo ErrorHandler
ActiveSheet.Unprotect

Dim linkpath, homefile, masterquarterfile, quartermonth, sumsheet As Variant

linkpath = Application.ActiveWorkbook.FullName
homefile = ActiveWorkbook.Name
sumsheet = ActiveSheet.Name

MsgBox "Please select the Quarterly Template file you wish to open",
vbOKOnly, "Select File"
masterquarterfile = Application.GetOpenFilename()
A = MsgBox("Open " & masterquarterfile & "?", vbYesNoCancel, "Open Quarterly
Template file")
If A = vbNo Then
'GoTo ErrorHandler
Exit Sub
Else
If A = vbCancel Then
'GoTo ErrorHandler
Exit Sub
End If
End If
Workbooks.OpenText masterquarterfile
masterquarterfile = ActiveWorkbook.Name

Windows(homefile).Activate
Sheets(sumsheet).Select
ActiveSheet.Unprotect
linkpath = Application.ActiveWorkbook.FullName
Sheets(sumsheet).Copy After:=Workbooks(masterquarterfile).Sheets("front" )
ActiveSheet.Shapes("UpdateSummary").Select
Selection.Delete
ActiveSheet.Shapes("UpdateQuarters").Select
Selection.Delete

MsgBox linkpath

ActiveWorkbook.BreakLink Name:=linkpath, Type:=xlLinkTypeExcelLinks <=ERROR
LINE

etc etc