UPdate Links every 10 minutes in excel

[wdverner]

Hi all,
im having some trouble getting the timer func to work in a macro in excel.

What i want to do it set it up to run automatically every 10 minutes to refresh the links in the worksheet..

ActiveWorkbook.UpdateLink Name:="C:\Master.xls", Type:= _
xlExcelLinks
How can I achieve this?

Many thanks for your help and guidance

 

[SkipVought]

Hi,

im having some trouble getting the timer func to work in a macro in excel.

What have you tried so far? Where is your code?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[GlennUK]

And what timer func have you tried exactly? What you should be doing is experimenting with the OnTime method. Is that what you've being doing? And if so, can we see what you tried before we all start trying to fix it, please?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

Please bear in mind comments made by Skip and Glenn.

But in the meantime, as I'd experimented to get to it, here's something you could adapt. The PauseTime for 10 minutes 600. Beware of the endless loop in this code though. It is possible to escape and it's also possible to do other work due to the use of DoEvents.

However this code will be a drain on system resources and may not be the best solution ultimately!

Sub ooo()
Dim PauseTime, Start

PauseTime = 5   ' Set duration.
Start = Timer   ' Set start time.

Do
    [a65536].End(xlUp).Offset(1) = Format(Now(), "hh:mm:ss")
    Do While Timer < Start + PauseTime
        DoEvents    ' Yield to other processes.
    Loop
    Start = Timer
Loop
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[GVF]

I'm thinking you want an OnTime as Skip suggests....

Sub Autpen() 'Runs everytime you open the workbook. You could put it in an OnSheetActivate or something similar
Call SetOnTime()
End Sub

Sub SetOnTime()
Application.OnTime Now + TimeValue("00:10:00"), Procedure:="MyLinkUpdate"
End Sub

Sub MyLinkUpdate()
ActiveWorkbook.UpdateLink Name:="C:\Master.xls", Type:= _
xlExcelLinks
Call SetOnTime
End Sub

Sub Auto_Close()
ActiveWorkbook.UpdateLink Name:="C:\Master.xls", Type:= _
xlExcelLinks
Call ClearOnTime
End Sub

Sub ClearOnTime()
Application.OnTime Now + TimeValue("00:10:00"), _
Procedure:="MyLinkUpdate", Schedule:=False
End Sub

But what happens if you switched workbooks and the active one isn't the one you want the links updated from when the OnTime event runs? Make it bulletproof.

Greg