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

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

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

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

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