Excel Spreadsheet display with autorefresh

[Adam]

Hi,

I have 2 spreadsheets, one of which is to be displayed on a monitor at all
times for informational purposes. The other spreadsheet is an export file
from microsoft access.

The spreadsheet being displayed contains data linked to the export file. I
can't seem to get the display spreadsheet to auto update itself after a
specific period of time. The spreadsheet will only update whenever the export
file is opened.

Is there a way to auto update/refresh the display spreadsheet (always open),
linked to the exported spreadsheet file (always closed), without having to
open and close the exported spreadsheet file?

The exported spreadsheet file is updated frequently via microsoft access.

Thanks in advance.

 

[Alan McQuaid via OfficeKB.com]

Hi Adam,

You could use a macro to open and close the workbook and to run that macro on
a regular basis you could take a look at using the OnTime method, see
http://www.cpearson.com/excel/OnTime.aspx

Alan

 

[Mike K]

Heres the code I use to refresh my worksheet for SQL calls every 2 seconds.
It might work for you. I have mixed results in updating links to workbooks.

Theres a start macro that starts on open ( I added a button), a stop macro
(also a button) and a keep refreshing macro. Theres some code in there to
color cells to let me know the status, and park the active cell.

Hope this helps,

Mike

Dim KeepRefreshing As Boolean

Sub StopRefresh()
Range("A2").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E2").Select
KeepRefreshing = False
End Sub

Sub AutomaticRefresh()
If KeepRefreshing Then
Application.OnTime Now + TimeValue("00:00:02"), "RefreshThisSheet"
End If
End Sub
Sub StartAutoRefresh()
' This function sets it all going, by setting the flag and
' making the initial call to RefreshThisSheet
Range("A2").Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E2").Select
KeepRefreshing = True
RefreshThisSheet
End Sub

Sub RefreshThisSheet()
' This function refreshes the sheet, and then calls the
AutomaticRefresh
' function, which will call it back in a couple of seconds
Application.ScreenUpdating = False
Application.CalculateFull
AutomaticRefresh
End Sub