Intro
Reports Controller or Power Refresh helps to schedule update of Excel files regardless of content. It can be a simple file with formulas, file with external links, connections, data model (aka PowerPivot), anything else.
All good, when you have a limited number of files and can refresh them manually. But what if you became sick of this routine: same files, same sequence of actions, saving, emailing etc. All of these – everyday. Why not to automate this process?
As I work a lot with Excel-based reporting, at some point I started to seek for the best solution to refresh Excel files on schedule / on working days / flexible / that will refresh Power Query connections / refresh PowerPivot data model /. Solution, which I can adjust to handle specific needs / that does not require admin rights (critical for enterprises). I gave up to find such software, and decided to create my own.
Latest release version is available here.
Why not Windows Task Scheduler?
In the beginning, I tried to work with tasks scheduled in Windows Task Scheduler, which were executing VBScript with parameters, like path to Excel workbook, macro before/after etc. VBScript were opening target workbook and trigger RefreshAll method.
Main drawbacks of working with Windows Task Scheduler
- Requires admin rights. Interesting that creation of folders and tasks doesn’t require admin rights, but modification or deletion does. If you work in big company in business department (not IT), by default you don’t have admin rights, and have low chance to get it. So, needed a solution without admin rights.
- Hard to maintain many tasks. List of tasks in Task Scheduler is not a table with parameters, you may see certain parameters, but you need to open each task to change them.
- No option to schedule tasks on working days. Workaround is to embed ‘factory calendar’ in each workbook and check before refresh whether report date is working date or not. But this is not my way.
Once, hard disk on my reporting workstation died and I had to restore all tasks on new workstation. Fortunately, I had backups. It wasn’t a simple task, however I found some scripts that helped me. In general, Backup / Restore and Transfer tasks from one workstation to another workstation is not what you would dream to do.
How I came up with idea to develop scheduler in Excel
I wanted a solution that would not have inconvenience existing in Windows Task Scheduler. Another problem with old method was related to VBScript limitation. It has limited control over Excel application, it cannot handle Excel’s runtime errors. So, I’ve decided that the best way to refresh Excel files is from Excel itself.
I needed “Mission Control Centre”, where I would enter list of my tasks/reports with all required for refresh parameters. It was easy – just a table, one row – one report (task), columns contain parameters. Number of parameters grew with time, and I started to put them in groups – Schedule / Saving / Extra Options.
How solution works – shortly
In general, refresh of Excel file is a simple task.
- Create new instance of Excel application. Excel is not the most stable application, so best practice is to use new Excel process each time to avoid memory garbage collection that leads to crash.
- Open target workbook
- Run ActiveWorkbook.RefreshAll (with disabled background refresh)
- Save workbook – Quit / Kill Excel process
But this is just a theory.
First of all, we need something that will run in a loop and check which task/report has to launched. Let’s say each minute it scans table and checks ‘Next Run Date Time’. When value of ‘Next Run Date Time’ is in the past – it is time to refresh report. API Timer helps to keep Excel interactive while it scans table, launches new Excel instances etc.
Secondly, when we launched refresh of certain report, we need to calculate ‘Next Run Date Time’. It means that we need to define schedule for each report.
Simple schedule, what allows to define scenarios like “Every Day”, “Every 7 days” may look like
However, real life requires more complex scenarios, like
- “From 1st to 4th working days of month and in last two – each month, but not in January. Working days of Australian office.”
- Every day, every 10 minutes, from 7am to 6pm (local time).
Thirdly, when workbook is refreshed, we need to save it somewhere. So we need to define saving scenario – in saving options
Fourthly, what if we need to run macro before RefreshAll or after, or skip RefreshAll, or something else, like pass parameters to Named Ranges – welcome to Extra Options
How it works – in details
This will require a series of articles. Stay tuned, follow my blog or Twitter @_Ivan_Bond
Ivan Bond's blog 