Updating sheet without re-opening...

[neilcarden]

Hi,

I have a workbook that is open constantly on our helpline wallboard. A
couple of the cells point to other cells in a different workbook which
we update daily.

My problem is that when we make the updates the sheet on the wallboard
doesn't update unless I close and re-open, or I open the other sheet on
that pc. Is there any way of auto refreshing and pulling the data?

I've tried the F9 (calculate) in a macro that runs every 15 minutes,
but it doesn't refresh the data from the other sheet....

Any ehlp would be appreciated...
Thanks

 

[Tom Hutchins]

Instead of just recalculating, your macro needs to call the UpdateLink
method, like this:

Sub Auto_Open()
Application.OnTime (Now() + TimeValue("0:01:00")), "UpdLinks"
End Sub

Private Sub UpdLinks()
'Update all links in ThisWorkbook.
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Calculate
'If ThisWorkbook isn't active any more, stop scheduling updates.
If ActiveWorkbook.Name = ThisWorkbook.Name Then
Application.OnTime (Now() + TimeValue("0:01:00")), "UpdLinks"
End If
End Sub

The example above is set to update approximately once a minute. For 15
minutes, change the TimeValue argument to "0:15:00".

Hope this helps,

Hutch