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

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

It is. Such a simple solution. I was aware of ActiveWorkbook, but not
ThisWorkbook. Perfect. Thanks.

"ben" wrote:

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

your welcome,
as my friend says, "Blonde is good". simple is best

"xlcharlie" wrote:

It is. Such a simple solution. I was aware of ActiveWorkbook, but not
ThisWorkbook. Perfect. Thanks.

"ben" wrote:

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