Timing of events on different levels

gkis2

New Member
Joined
Dec 5, 2018
Messages
17
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

gkis2

New Member
Joined
Dec 5, 2018
Messages
17
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

New Member
Joined
Dec 5, 2018
Messages
17
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,912
Members
414,346
Latest member
mmoose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top