BreakLink method for 2007

[Domenick]

I have several workbooks in Excel 2003 that use the BreakLink method as
follows:

Worksheets(1).Calculate
ActiveWorkbook.BreakLink Name:="S:\Financial Analysis\Labor
Report2.xls", Type:= _
xlExcelLinks
ActiveWorkbook.Save

My machine was just upgraded to Excel 2007 and VBA no longer supports the
BreakLink method. Can anyone provide a workaround for the above that will
work in 2007?

Thank you.

 

[Ron de Bruin]

Hi Domenick

No problem in 2007 with this macro

I see you use xlExcelLinks, I think that is your problem

Sub Break_Links_To_other_Excel_Workbooks()
'This example convert formulas that point to another Excel workbook to values
'It will not convert other Excel formulas to values.
'Note that BreakLink is added in Excel 2002
Dim WorkbookLinks As Variant
Dim wb As Workbook
Dim i As Long

Set wb = ActiveWorkbook

WorkbookLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
If IsArray(WorkbookLinks) Then
For i = LBound(WorkbookLinks) To UBound(WorkbookLinks)
wb.BreakLink _
Name:=WorkbookLinks(i), _
Type:=xlLinkTypeExcelLinks
Next i
Else
MsgBox "No Links to other workbooks"
End If
End Sub

 

[Domenick]

I'm not sure I fully understand your code. Will your macro break all links to
all other workbooks? What if I just want to break the link to a particular
workbook?

Thanks for your help.

 

[Ron de Bruin]

Yes this example break them all

You can use the Help example and change the name

Sub UseBreakLink()

Dim astrLinks As Variant

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks

End Sub

 

[Domenick]

Perfect - that did the trick!

Thanks for your help.

-Dom