# auto refresh on excel spreadsheets



## planningman40 (Jun 3, 2008)

at my office I created a spreadsheet that mimics the transport board (clip boards in a back office) and my boss wants to be able to have a screen in despatch showing this spreadsheet whilst being able to update the spreadsheet on his pc..... and having the screen in despatch update with any new additions

is there a way of setting up the spreadsheet to refresh every couple of mins so the screen in despatch is always current.

we are using windows 2000 

thanks in advance for any help


----------



## DiscWorld (Jun 2, 2008)

I have a spreadsheet that displays information from lots of linked spreadsheets, and toggles between sheets every minute, but you should be able to modify the code slightly for what you want.

--- MACROS ---

' General Declarations
Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' seconds between updates
Public Const cRunWhat = "UpdateLinks" ' name of procedure to run
Public Toggle As Double

' Ensure Timer Starts when Workbook is opened
'
Private Sub Autpen()
Call StartTimer
End Sub

' Timer Program
' Macro recorded 21/11/2007 by W. Sheldon
'
Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalQuick)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True

End Sub

Sub UpdateLinks()
'
' Update data from external files
'
Application.ScreenUpdating = False

ActiveWorkbook.UpdateLink Name:= _
"\\SPREADSHEET PATH AND NAME.xls", Type:= xlExcelLinks

Application.ScreenUpdating = True

End Sub


----------



## planningman40 (Jun 3, 2008)

hi..

thanks for the macro.. but I am having trouble with..

Activeworkbook.updatelink name:="f:\production planning\transport sheet wk23.xls",type:=xlexcellinks


when i run the macro is says this line has failed

what have I done wrong?


----------



## DiscWorld (Jun 2, 2008)

You need a cell referencing the remote spreadsheet in your current spreadsheet, otherwise is errors because there is nothing to updated.

If you are using a SHARED workbook, that doesnt link to any other spreadsheets, then the Advanced tab of the Shared Workbook option has an "Update changes every x minutes" option, and you wouldnt need any macros.


----------

