Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Update links on a network
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Update links on a network
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. "AZSteve" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Update links on a network
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. "JLGWhiz" wrote: 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. "AZSteve" wrote in message ... 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Update links on a network
Just guessing, but check the link sources to see if they are still valid
links. They sometimes get broken inadvertantly. "AZSteve" wrote in message ... 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. "JLGWhiz" wrote: 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. "AZSteve" wrote in message ... 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Update links on a network
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. "JLGWhiz" wrote: Just guessing, but check the link sources to see if they are still valid links. They sometimes get broken inadvertantly. "AZSteve" wrote in message ... 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. "JLGWhiz" wrote: 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. "AZSteve" wrote in message ... 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update workbook links over network | Excel Worksheet Functions | |||
Using Macro to update Links | Excel Programming | |||
A macro to update links | Excel Programming | |||
Update links macro | Excel Programming | |||
Macro after Update of Links | Excel Programming |