Problem with the Application.OnTime Method

[Sargad_Strut]

Hey guys,

I have this thing I'm struggling with. I want to automate a daily routine that is taking forever to do "manually". Basically we have ten workbooks that each need to be opened, updated, and saved to file separately. The workbooks all contain links and query connections.

When you open one of the files manually, it takes some 45 seconds for each and every one to update. Thereafter you would click a button that will update the query tables, after which you would click another macro button that saves the output sheet to file. But when I open the workbooks from the single "overview" sheet I've created, the first update won't run by itself, which is why I had to add Application.OnTime in order for it to run in the correct order and prevent errors. See code below:

Private Sub Auto_Open()
    UpdateAndSave
End Sub
 
Sub UpdateAndSave()
   
    Range("A8").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    ActiveWorkbook.RefreshAll
    Application.OnTime Now() + TimeValue("0:01:00"), "UpdateDatafile1"
    Application.OnTime Now() + TimeValue("0:02:00"), "SaveDatafile1"

This part is doing exactly what I want it to. But it creates a problem with the other nine. I'd basically like to run the code above all over again, starting with A9 for Datafile2. I guess I could add an OnTime at the end of SaveDatafile1, in order for it to run another macro. But then I'd have a never-ending tree of delayed macros. There's probably a number of solutions to this. Maybe one improvement could be to create some sort of timer to make the delays less static?
Or is there a dirty way of doing the equivalent of
Application.OnTime Now() + TimeValue("0:01:00"), Range("A9").Select, in order for me to just accomplish what I want to do?

Any suggestions would be very much appreciated!
Thank you for your time

 

[mole999]

personally I would create 10 subs, one for each workbook (generally identical), and call each one in sequence, easy to turn one off if it's causing a problem or needs specific actions, or debug just using one routine

 

[Sargad_Strut]

Yeah, I guess that'll work. Then I can just add a normal Range.Select at the beginning of every sub, since they will be delayed themselves. Any thoughts on how to make the application stay idle while RefreshAll is running (so I don't have to waste extra time to have some margin of safety, as I do now)? Application.Wait isn't working, seems to just pause everything.

Thank you for your reply!

 

[mole999]

I've used DoEvents can't remember if someone on here said there was a better way