W
whylite
Just wondering if anyone can help me with this. I have a file template that
is linked to another template's new book. The template is paperwork.xlt and
the link is to paperwork1. If I use the edit links in the toolbar the change
link works. My macro works when the link is to a file.xls but fails with an
error message telling me I need to unprotect the sheet. If I write the
command to unprotect the sheet the error msg is removed but the link is still
left unchanged. Below I have left my code. Any idea's would be appreciatted.
Sub Restorelinks()
On Error GoTo ErrorHandler
Dim PATH, link, savefilename As String
Application.ScreenUpdating = False
Const iTitle = "Link To New Workbook" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, , iTitle,
vbOKCancel)
If PATH = "False" Then
MsgBox "You have chosen to not restore Links. If this is a mistake you
will have to start over.", vbOKOnly, "Restore Link Failure"
Exit Sub
End If
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
link = aLinks(i)
Next i
End If
ThisWorkbook.ChangeLink link, PATH, xlExcelLinks
Application.ScreenUpdating = True
ErrorHandler:
If Err.Number <> 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub
is linked to another template's new book. The template is paperwork.xlt and
the link is to paperwork1. If I use the edit links in the toolbar the change
link works. My macro works when the link is to a file.xls but fails with an
error message telling me I need to unprotect the sheet. If I write the
command to unprotect the sheet the error msg is removed but the link is still
left unchanged. Below I have left my code. Any idea's would be appreciatted.
Sub Restorelinks()
On Error GoTo ErrorHandler
Dim PATH, link, savefilename As String
Application.ScreenUpdating = False
Const iTitle = "Link To New Workbook" ' title of dialog box
Const FilterList = "Microsoft Excel Workbook (*.xls),*.xls"
PATH = Application.GetSaveAsFilename(savefilename, FilterList, , iTitle,
vbOKCancel)
If PATH = "False" Then
MsgBox "You have chosen to not restore Links. If this is a mistake you
will have to start over.", vbOKOnly, "Restore Link Failure"
Exit Sub
End If
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
link = aLinks(i)
Next i
End If
ThisWorkbook.ChangeLink link, PATH, xlExcelLinks
Application.ScreenUpdating = True
ErrorHandler:
If Err.Number <> 0 Then
MsgBox "error occured"
Exit Sub
End If
End Sub