Breaking excel links in code?
 

Anyone know how to break external links from one spreadsheet to
another in Excel. I have used the code
ChDir "C:\Documents and Settings\fdxuser\My Documents
\Flight Loads\"
ActiveWorkbook.BreakLink Name:=FileToOpen,
Type:=xlExcelLinks
But I get an error msg and it doesn't actually break the links...

Thanks,
chet

Breaking excel links in code?
 

A quick question, what is FileToOpen? I don't see it anywhere, is
that supposed to be the path to the activeworkbook? I also don't know
i
I used the following code, and it broke the links for me:

Sub BreakLinks()
Dim Awb As Workbook
Dim aLinks

Application.ScreenUpdating = False
Set Awb = ActiveWorkbook
'Get an array of the external links
aLinks = Awb.LinkSources(xlExcelLinks) 'this will return empty if
'there are not external links
'As long as the array is not empty, loop through the array and change
'the reference
If Not IsEmpty(aLinks) Then
For i = LBound(aLinks) To UBound(aLinks)
'error catching in case the external link cannot be changed
'(if the link references a worksheet
'that exists in the linked workbook, but does not exist in the
'active workbook, it will cause an
'error and the link will not be changed
On Error Resume Next
'break links
Awb.BreakLink Name:=aLinks(i), Type:=xlLinkTypeExcelLinks
On Error GoTo 0
Next i
End If
Application.ScreenUpdating = True
Erase aLinks
aLinks = Awb.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then MsgBox "There are still external links in"
'this workbook"
End Sub
On Mar 12, 8:00 pm, "Chet" wrote:
Anyone know how to break external links from one spreadsheet to
another in Excel. I have used the code
ChDir "C:\Documents and Settings\fdxuser\My Documents
\Flight Loads\"
ActiveWorkbook.BreakLink Name:=FileToOpen,
Type:=xlExcelLinks
But I get an error msg and it doesn't actually break the links...

Thanks,
chet

Breaking excel links in code?
 

FileToOpen is the name of the variable for the file I was opening.
I'll try this.. Thx! Chet

On Mar 13, 6:40 am, "meatshield" wrote:
A quick question, what is FileToOpen? I don't see it anywhere, is
that supposed to be the path to the activeworkbook? I also don't know
i
I used the following code, and it broke the links for me:

Sub BreakLinks()
Dim Awb As Workbook
Dim aLinks

Application.ScreenUpdating = False
Set Awb = ActiveWorkbook
'Get an array of the external links
aLinks = Awb.LinkSources(xlExcelLinks) 'this will return empty if
'there are not external links
'As long as the array is not empty, loop through the array and change
'the reference
If Not IsEmpty(aLinks) Then
For i = LBound(aLinks) To UBound(aLinks)
'error catching in case the external link cannot be changed
'(if the link references a worksheet
'that exists in the linked workbook, but does not exist in the
'active workbook, it will cause an
'error and the link will not be changed
On Error Resume Next
'breaklinks
Awb.BreakLink Name:=aLinks(i), Type:=xlLinkTypeExcelLinks
On Error GoTo 0
Next i
End If
Application.ScreenUpdating = True
Erase aLinks
aLinks = Awb.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then MsgBox "There are still external links in"
'this workbook"
End Sub
On Mar 12, 8:00 pm, "Chet" wrote:



Anyone know how tobreakexternal links from one spreadsheet to
another in Excel. I have used the code
ChDir "C:\Documents and Settings\fdxuser\My Documents
\Flight Loads\"
ActiveWorkbook.BreakLink Name:=FileToOpen,
Type:=xlExcelLinks
But I get an error msg and it doesn't actuallybreakthe links...

Thanks,
chet- Hide quoted text -

- Show quoted text -