Macro Excel Scheduling - Love and Hate It

Automation is fun. 

Right?
If you are reading this, I am willing to bet most of you will agree me. I doubt I am alone in finding satisfaction by watching a well designed automated process and supply chain .

If you are struggling to understand my nerdy obsession, watch the below video detailing how candy canes are made:

Not too long ago, every single one of those steps had to be done manually by a person. Today, humans just play a part as a few cogs of a long automated process. 

This type of automation innovation is still going on today. Whether it the agriculture industry using automated picking robots, to Tesla attempting to fully automate car production. 

Let's get back to lovely Excel

Unfortunately unless you are a robotics engineer or a "supply chain manager" (whatever they do), we don't get to play with robotic machinery. Instead, we get the pleasure of using scripts to automate tedious data and IT tasks. 

I am not being sarcastic in that last sentence. It is a great feeling when you come into a workplace, use basic macros to save hours of previous manual tasks. For example I have used a combination of Google Sheets, Scripts, Excel and Task Scheduler to fully automate data entry and reporting. Previously it was all paper based, now they just enter it directly into an Ipad (which also data validates) and when they submit it, a report on Excel is automatically sent out to managers. 

It's great. Macros are great. 

"Good job data guy - now I want daily reports"

Explicits. I don't want to manually copy and paste data to Outlook, and then send them out. Yes it only takes roughly one minute daily, but instead I want to spend 10 hours setting up something to automate it. That's normal right?

My data is all in Excel; after all everybody knows it is a great database tool -yes that is sarcasm, but I am not out to change the world of businesses storing everything on Excel. Just to automate things. 
Which means I need Excel to help me spit out daily reports.

This is relatively easy to do, and plenty of macros out there that can do it (ironically not yet on this site, this will be my next one I promise). The hard part I found was how do I get a macro to automatically run at 8:00am, even if Excel isn't open? Auto_open macro exists, which launch the macro as soon as the workbook is open, so the only question is how do I automatically open the Excel Workbook at the specified time?

Task Scheduler is the answer. It is on every Windows machine, and it is simple to use. 
Lucky for you, I wrote an Macro Scheduling tutorial. Go have a read and learn how to automatically back up spreadsheets. 

Task Automation Successful...

What if the computer is off you ask? Hush - what are you trying to do? Save power? Leave it on 24/7. (Or get your IT department to make a virtual computer that is on 24/7 - however then when your IT department has constant issues and server crashes your boss gets pissed off when the reports get missed). 

Macros running on your computer are annoying? I do admit, when you are under pressure trying to get something done and task scheduler fires a macro that takes a few minutes to load, you get very frustrated. Or you are tying to show your manager something, he comes over, macro fires and you just sit there for 5 minutes awkwardly waiting. Definitely better if on a second computer.

So am I saying it isn't that great? It can be if constructed properly. Excel isn't designed to be an automated cloud email service (believe it or not). The best macros I have used, have been automating tasks that you commonly do in Excel. Being creative and writing macros that go above that, often seem to run into problems. 

If you have identified that yourself - congratulations. You are now too advanced for Excel. 
Don't have an existential crisis just yet though.
Look into Google Sheets, as a proper cloud platform automated email/reporting are so much more reliable. After that, look into Power Bi to start learning about databases, dashboards and proper decision support systems for managers. 

How confident am I on my ramblings? Well, try my confidence interval calculator and you tell me.