Timing of events on different levels

[gkis2]

Background is that our IT has allowed us non IT types to get to some SQL data which I get with a Data connection in Excel. I would love to pipe it straight into MS Access but I imagine even to humbly request that would create a dread sense of panic amongst them and I would subsequently live in fear of 'virtual' removal from the organization. Yes, they are well connected to HR.

My main goal is to process this data transfer automatically in the wee hours of the morning so our team will have access (pun?) to the data when they start their day.

So I am bit new to this method of getting data into Excel but so far it seems that I have been successful. I then use the Excel query to trim the data down the stuff I need which is very valuable as one of the tables has over 200K rows. This creates one of the timing issues. I have to transfer the SQL data in first then I need to run the queries that trim away unwanted data and do some data type conversions. I have written a very simple macro that just calls for all four connections to be refreshed. First two are sequel data and last two are the queries in Excel. Maybe I'm already OK but I don't know if these refreshments are occurring one at a time in the correct order? Can someone let me know the answer to that question?

Next question is how to program Excel to do that at 4:00 AM without having to drive here that early (I'm not usually awake). I wrote a Workbook-Open macro for that.

 

[gkis2]

Private Sub Workbook_Open
Application.OnTime TimeValue ("5:00:00"), Macro3
End Sub

That is all I have so far. I haven't used 'OnTime before so I don't know what to expect exactly. Is it the case that as long as I leave this Excel file open it will run the macro each day at 5:00 AM? Does it know when 5:00 AM is or does it just count 5 hours after the workbook is opened?

Also I'd like to have some confirmation that it ran when I come in tomorrow. I'm thinking there may be some way to look at the data connections to see when they were last updated. I tried to quickly look around the Data tab but didn't find anything. I'll keep looking but would appreciate if someone could tell me how to find that. I may also try to have the macro send me an email. I know how to do that in MS Access and suspect it will be similar in Excel. Thank You

 

[gkis2]

So far just replying to my own threads but wanted to give an update. I did find a way to fire off an email. Of course the macro fires it off milliseconds after it starts the refresh actions. This is fine, I'll know the macro ran and really I'm just not used to 'OnTime so this is just confirmation for me that it is working. It would be nice, however, if this would only run after the data refresh action has successfully completed. Is there a way to programmatically check when the data operations are complete so I can fire off my email after the updates are complete? I also want to give a clear visual indication on the Excel sheet if I am trouble shooting and staring at this thing as it updates. I know how to shoot our a MsgBox, just don't know how to make it so it appears after the updates are complete. Help is greatly appreciated