Updating Links

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi we have a few different workbooks that link to each
other. How do we set it up so that when one workbook is
saved with new material it will appear in the appropriate
info will appear in another workbook without having to
close and re-open either workbook?

Thanks
Ben
 
Thanks Dave,

Is there a way to set the file to auto update either when
it notices a change or on a certain schedule, every 10
minutes, hourly etc.

Ben
 
Not that I'm aware.

And I think that the "sending" workbook would have to be saved.

Maybe a workaround is to have an "ontime" macro run every 10 minutes that
refreshes links.

Chip Pearson has lots of nice instructions for OnTime procedures at:

http://www.cpearson.com/excel/ontime.htm

Stolen shamelessly from Chip's page and the help for updatelinks

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 600 '10 minutes
Public Const cRunWhat = "MyRefresh"
Sub auto_open()
Call StartTimer
End Sub
Sub auto_close()
Call StopTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
Sub myRefresh()
With ThisWorkbook
.UpdateLink Name:=.LinkSources
End With
Call StartTimer 'get ready for next time
End Sub

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thanks Dave,

I will look into this!

Ben
-----Original Message-----
Not that I'm aware.

And I think that the "sending" workbook would have to be saved.

Maybe a workaround is to have an "ontime" macro run every 10 minutes that
refreshes links.

Chip Pearson has lots of nice instructions for OnTime procedures at:

http://www.cpearson.com/excel/ontime.htm

Stolen shamelessly from Chip's page and the help for updatelinks

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 600 '10 minutes
Public Const cRunWhat = "MyRefresh"
Sub auto_open()
Call StartTimer
End Sub
Sub auto_close()
Call StopTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
Sub myRefresh()
With ThisWorkbook
.UpdateLink Name:=.LinkSources
End With
Call StartTimer 'get ready for next time
End Sub

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Back
Top