xlcharlie,

Is the macro that the timer runs actually in the workbook that changes? If
it is then you can use
thisworkbook.name
no matter what that name is



"xlcharlie" wrote:

I am trying to use code to have the links in my spreadsheet update
automatically. I've been using Application.OnTime Now +
TimeValue("00:10:00"), "updateLinks" to call a procedure that does just that,
but here's where I am having trouble. I want to be able to leave the
spreadsheet open in the background while I work in other windows and Excel
spreadsheets. For the code I am using to work, I have to activate the
workbook I want to refresh. I can simply add code referring to it by name,
but I'd like something more flexible. I'd like to assign a variable to the
workbooks name (x = ActiveWorkbook.Name) and then retain the value of that
variable as the code continues to cycle. That way the file name can change
and the code will still work. I know how to pass a variable's value when
calling a procedure, but the OnTime method does not seem to allow me to do
that the same way. Is there another method I could use? Any code that will
automatically execute every so often while the workbook is open would be
appreciated. Here's what I've been trying to make work:

Sub refreshTimer()
x = ActiveWorkbook.Name
Application.OnTime Now + TimeValue("00:00:03"), "updateLinks"
End Sub

Sub updateLinks()
Workbooks(x).Activate
ActiveWorkbook.UpdateLink Name:="G:\BOGAC\SWAP05\RISKJEN.xls", Type:= _
xlExcelLinks
Call refreshTimer
End Sub