Macro to Update links on a network

  • Thread starter Thread starter AZSteve
  • Start date Start date
A

AZSteve

I recorded this macro as I did an update. Why do I get an error message when
I try to play it back. I want to have an "update" macro which will update
cells that are linked to 3 separate workbooks which may be open or closed.

Sub UpdateLinksV2()
ActiveSheet.Unprotect
ActiveWorkbook.UpdateLink Name:= _
"O:\folder\subfolder\Time off Calendar\Time Off Calendar.xls",
Type:=xlExcelLinks
ActiveWorkbook.UpdateLink Name:= _
"O:\folder\Call In Line - Updates 2010.xls", Type:=xlExcelLinks
ActiveWorkbook.UpdateLink Name:= _
"O:\folder\subfolder\Staffing-Master.xls", Type:=xlExcelLinks
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

The error I get is Runtime Error '1004' Method 'UpdateLink' of object
'_Workbook' failed.

The "O" drive is actually mapped to \\server\share but substituting that for
"O:" does not help.
 
Are "Folder'" and "SubFolder" actual directory names? If not, are they
supposed to be variable string names? If they are, then they would need to
be declared and initiated somewhere prior to using them in the path.
 
Yeah, they represent actual directory names in my macro. I just didn't want
to put the actual names in considering this is a public forum.
 
Just guessing, but check the link sources to see if they are still valid
links. They sometimes get broken inadvertantly.
 
I'm not sure what you mean by checking the link sources because they
sometimes get broken. The message on Edit/Links is that it can't find the
linked files, so every time I have to point to them again even though they
haven't changed. And I have to do it manually because the macro doesn't
work. I and others use this file throughout the day and we would like the
updating of links to not be so manual.
 
Back
Top