changelink macro failing. Prior link is to a non existent file.

[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

 

[Tom Ogilvy]

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

 

[whylite]

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

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

 

[Tom Ogilvy]

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

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.

 

[whylite]

Paperwork.xlt is the first template. Sheets.xlt is my second template. If
you open new the paperwork and the sheets then sheets will import anything
typed into paperwork1. This is how I built the link. Sometimes after
filling out these spreadsheets and saving them with new names the link gets
damaged because people close them and move them into other folders(thats why
the getsaveasfilename) so one can browse to the new folder and restore the
link. In an ideal world anyone using these sheets will have allready saved
them and the link will be to an xls and the macro will work. I can just see
someone opening a new sheets file and trying to change the link in this case
the macro fails because the link is Paperwork1 and it is to a unsaved file,
yet if you change source in the edit links it works perfect. The recording
gives every indication that the macro I have should work. I think that
excel's built in programming is doing something else to avoid the error and
is not putting it in the recording. My understanding is that everything that
excel can do can be copied in vba. Thanks for you help.