Macro to Update links on a network

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.
 
J

JLGWhiz

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.
 
A

AZSteve

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.
 
J

JLGWhiz

Just guessing, but check the link sources to see if they are still valid
links. They sometimes get broken inadvertantly.
 
A

AZSteve

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top