Automation of repetitive actions is an important part of work of every advanced Excel user willing to stay productive. Sometimes it is a single-line macro performing one specific action, sometimes – massive VBA code developed to optimise certain business scenario.
There are plenty of good web sites and online courses helping to beginners.
I focus on automation of Excel-based reporting, when non-IT reporting specialist / business user has to update multiple Excel workbooks regularly with minimum effort.
I assume that each workbook is designed in a way that refresh can be done without manual interactions. With Power Query it is much easier now.
Schedule Refresh of Excel files
I worked in a company where Excel-based (Self-Service) reporting was the main way of reports preparation for all levels of users. Everything I designed colleagues wanted to see refreshed on regular basis. Those were different reports (Excel workbooks) – some with connection to internal SQL data bases / Excel files / CSV files, some – with Power Pivot and sophisticated Data Model and DAX measures, some – just a simple Power Query queries loaded to worksheets and so on.
That situation forced me to develop a solution that would manage refresh of multiple Excel workbooks with unique logic.
I mainly work with Excel and VBA, so I decided to stick to these two technologies. Firstly, because Excel gives flexibility for maintenance of parameters needed for reports (as any parameter value can be generated by formula), secondly, because VBA + Win API functions give enough flexibility to control refreshing process.
You can read more and download Power Refresh solution from its official page on Github.
Automation of SAP Analysis Excel add-in
Another solution has been developed to automate SAP BO Analysis add-in. In particular, refreshing workbooks with data sources linked to SAP BW / SAP BO. It can be downloaded from its Github page.