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