Repost - Updating Links on a network (original 4/9/10)
I recorded a macro as I did an update for links (Edit Links) which are on a
network. 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. Same issue in both Excel 2003 (where macro recorded in a converted xlsm workbook) and Excel 2007. Still haven't gotten any suggestion that works. I have improved it to the point that it doesn't open the Edit Links box first to choose a link and then click "Update Values" (step 1) whcih then opens an Update Links box basically asking me to choose a file location (step 2). Now it goes directly to step 2. In eaither case, choosing shortcut to the network location in My Documents seems to be all it needs for each one. Why won't the macro work without having to make all these selections? Sub UpdateLinks() ActiveSheet.Unprotect ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Call In Line - Updates 2010.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Administration\T ime off Calendar\NPIC 2010_CRSs Time Off Calendar.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\WFM\Scheduling\Staff ing-Master.xls", Type:=xlExcelLinks ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub The path/file names shown above are as reported by using a LinkSources example in VBA Help (each one is shown in three consecutive message boxes). |
Repost - Updating Links on a network (original 4/9/10)
I can't test this for you because I don't have access to a network, but this
is what I used to use... Private Sub CommandButton3_Click() 'Sub SaveValuesToNetworkFile() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "North.xls" s = "VP - Chris" r = "I24:K26" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub Private Sub CommandButton4_Click() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "South.xls" s = "VP - Chris" r = "I30:K32" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "AZSteve" wrote: I recorded a macro as I did an update for links (Edit Links) which are on a network. 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. Same issue in both Excel 2003 (where macro recorded in a converted xlsm workbook) and Excel 2007. Still haven't gotten any suggestion that works. I have improved it to the point that it doesn't open the Edit Links box first to choose a link and then click "Update Values" (step 1) whcih then opens an Update Links box basically asking me to choose a file location (step 2). Now it goes directly to step 2. In eaither case, choosing shortcut to the network location in My Documents seems to be all it needs for each one. Why won't the macro work without having to make all these selections? Sub UpdateLinks() ActiveSheet.Unprotect ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Call In Line - Updates 2010.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Administration\T ime off Calendar\NPIC 2010_CRSs Time Off Calendar.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\WFM\Scheduling\Staff ing-Master.xls", Type:=xlExcelLinks ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub The path/file names shown above are as reported by using a LinkSources example in VBA Help (each one is shown in three consecutive message boxes). |
Repost - Updating Links on a network (original 4/9/10)
I'm not trying to copy to or from, I'm trying to update links (as in
Edit/Links) without having to do it manually. "ryguy7272" wrote: I can't test this for you because I don't have access to a network, but this is what I used to use... Private Sub CommandButton3_Click() 'Sub SaveValuesToNetworkFile() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "North.xls" s = "VP - Chris" r = "I24:K26" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub Private Sub CommandButton4_Click() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "South.xls" s = "VP - Chris" r = "I30:K32" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "AZSteve" wrote: I recorded a macro as I did an update for links (Edit Links) which are on a network. 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. Same issue in both Excel 2003 (where macro recorded in a converted xlsm workbook) and Excel 2007. Still haven't gotten any suggestion that works. I have improved it to the point that it doesn't open the Edit Links box first to choose a link and then click "Update Values" (step 1) whcih then opens an Update Links box basically asking me to choose a file location (step 2). Now it goes directly to step 2. In eaither case, choosing shortcut to the network location in My Documents seems to be all it needs for each one. Why won't the macro work without having to make all these selections? Sub UpdateLinks() ActiveSheet.Unprotect ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Call In Line - Updates 2010.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Administration\T ime off Calendar\NPIC 2010_CRSs Time Off Calendar.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\WFM\Scheduling\Staff ing-Master.xls", Type:=xlExcelLinks ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub The path/file names shown above are as reported by using a LinkSources example in VBA Help (each one is shown in three consecutive message boxes). |
Repost - Updating Links on a network (original 4/9/10)
http://www.ozgrid.com/VBA/update-links.htm
-- Regards Dave Hawley www.ozgrid.com "AZSteve" wrote in message ... I'm not trying to copy to or from, I'm trying to update links (as in Edit/Links) without having to do it manually. "ryguy7272" wrote: I can't test this for you because I don't have access to a network, but this is what I used to use... Private Sub CommandButton3_Click() 'Sub SaveValuesToNetworkFile() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "North.xls" s = "VP - Chris" r = "I24:K26" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub Private Sub CommandButton4_Click() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "South.xls" s = "VP - Chris" r = "I30:K32" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "AZSteve" wrote: I recorded a macro as I did an update for links (Edit Links) which are on a network. 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. Same issue in both Excel 2003 (where macro recorded in a converted xlsm workbook) and Excel 2007. Still haven't gotten any suggestion that works. I have improved it to the point that it doesn't open the Edit Links box first to choose a link and then click "Update Values" (step 1) whcih then opens an Update Links box basically asking me to choose a file location (step 2). Now it goes directly to step 2. In eaither case, choosing shortcut to the network location in My Documents seems to be all it needs for each one. Why won't the macro work without having to make all these selections? Sub UpdateLinks() ActiveSheet.Unprotect ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Call In Line - Updates 2010.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Administration\T ime off Calendar\NPIC 2010_CRSs Time Off Calendar.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\WFM\Scheduling\Staff ing-Master.xls", Type:=xlExcelLinks ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub The path/file names shown above are as reported by using a LinkSources example in VBA Help (each one is shown in three consecutive message boxes). |
Repost - Updating Links on a network (original 4/9/10)
Your link shows this
Sub OpenUpdate() Workbooks.Open FileName:= _ "C:\My Documents\LinkedBook.xls", UpdateLinks:=xlUpdateLinksAlways End Sub Is LinkedBook.xls the one I want to get the updates from or the one I want to update? If "from" I didn't understand that you need to open the workbook that you are getting the updates from. Some of them may be currently opened by other users on the network (yeah, I realize my info won't be current until they Save). I don't expect them up update my workbook (open or closed) every time they make entries into those other workbooks. If "want to update" it sems that this would be useless if I want to update it when I open it, or later on while it is still open. The problem is that when I click "Update Links" it can't seem to find any of the workbooks it is linked to so I have to manually point to each one every time, even in the same session. "ozgrid.com" wrote: http://www.ozgrid.com/VBA/update-links.htm -- Regards Dave Hawley www.ozgrid.com "AZSteve" wrote in message ... I'm not trying to copy to or from, I'm trying to update links (as in Edit/Links) without having to do it manually. "ryguy7272" wrote: I can't test this for you because I don't have access to a network, but this is what I used to use... Private Sub CommandButton3_Click() 'Sub SaveValuesToNetworkFile() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "North.xls" s = "VP - Chris" r = "I24:K26" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub Private Sub CommandButton4_Click() Dim p As String Dim f As String Dim s As String Dim r As String Dim ws As Worksheet p = "\\fsrv3\luck\public\Sales Operations\Ryan\" f = "South.xls" s = "VP - Chris" r = "I30:K32" Set ws = ActiveSheet Workbooks.Open (p & f) ' copy range values from current worksheet to same range in network file ws.Range(r).Copy Workbooks(f).Worksheets(s).Range(r) ' close network file, saving changes Workbooks(f).Close True End Sub -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "AZSteve" wrote: I recorded a macro as I did an update for links (Edit Links) which are on a network. 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. Same issue in both Excel 2003 (where macro recorded in a converted xlsm workbook) and Excel 2007. Still haven't gotten any suggestion that works. I have improved it to the point that it doesn't open the Edit Links box first to choose a link and then click "Update Values" (step 1) whcih then opens an Update Links box basically asking me to choose a file location (step 2). Now it goes directly to step 2. In eaither case, choosing shortcut to the network location in My Documents seems to be all it needs for each one. Why won't the macro work without having to make all these selections? Sub UpdateLinks() ActiveSheet.Unprotect ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Call In Line - Updates 2010.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\Administration\T ime off Calendar\NPIC 2010_CRSs Time Off Calendar.xls", Type:=xlExcelLinks ActiveWorkbook.UpdateLink Name:= _ "\\Phepsilon\Files_Restricted\WFM\Scheduling\Staff ing-Master.xls", Type:=xlExcelLinks ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub The path/file names shown above are as reported by using a LinkSources example in VBA Help (each one is shown in three consecutive message boxes). |
All times are GMT +1. The time now is 07:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com