I guess it wasn't clear that the problem is to an unsaved workbook. I
thought you said the problem was in linking to a template file. The code
you show picks up the name of the new link by using the Getfilename
procedure - but this wouldn't be possible if the file isn't saved.

The change source also used the File dialog - so I am not sure how you are
manually changing it to a unsaved workbook through the edit link process.

--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
I gave this a try and I am still getting the same error message. If the
link
in my spreadsheet is to a saved xls file then the macro works. If the
link
in the spreadsheet is to an unsaved book the macro fails, yet if I
manually
do the change source in the edit links the change goes through. I have
tried
recording the change source and it gives me no indication of what I might
be
doing wrong. Thanks for giving this a try for me if you have any more
ideas
send them my way. I know there has to be a way or it wouldn't work
manually
in the edit links.
--
Thanks!
Shane W


"Tom Ogilvy" wrote:

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
if instr(1,aLinks(i),"paperwork",vbTextCompare) then
link = aLinks(i)
End if
Next i
End If
if len(trim(link)) = 0 then
msgbox "Link not found"
exit sub
end if

ThisWorkbook.ChangeLink link, PATH, xlExcelLinks


--
Regards,
Tom Ogilvy


"whylite" wrote in message
...
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



--
Thanks!
Shane W