Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BreakLink method for 2007
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BreakLink method for 2007
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Domenick" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BreakLink method for 2007
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Domenick" wrote in message ... 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BreakLink method for 2007
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:=xlLinkTypeExcelLi nks) ' Break the first link in the active workbook. ActiveWorkbook.BreakLink _ Name:=astrLinks(1), _ Type:=xlLinkTypeExcelLinks End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Domenick" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Domenick" wrote in message ... 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BreakLink method for 2007
Perfect - that did the trick!
Thanks for your help. -Dom "Ron de Bruin" wrote: 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:=xlLinkTypeExcelLi nks) ' Break the first link in the active workbook. ActiveWorkbook.BreakLink _ Name:=astrLinks(1), _ Type:=xlLinkTypeExcelLinks End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Domenick" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Domenick" wrote in message ... 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveWorkbook.BreakLink runs but doesn't work?! | Excel Programming | |||
Need urgent help with VBA 2007 CHARTWIZARD method !!! | Excel Programming | |||
problems using printout method in excel 2007 | Excel Programming | |||
BreakLink | Excel Programming | |||
Why these commands make erros? (Breaklink and SaveAs) | Excel Programming |