Automated Excel report with Task Scheduler and Simple VBA functions
This topic might be useful for reporting specialists that use excel as a reporting tool on regular basis. This is a tutorial on how to create a fully automated self executing report that uses Windows Task Scheduler, Visual Basic for Applications and a library with simple VBA functions called Simple-VBA-functions. The scenario should represent automated creation of excel report that contains a single pivot table and a row data behind and it should be updated and uploaded to a Shared Folder on a daily basis. This is how the report look like: The first sheet "Report" shows the pivot data
The second sheet shows the raw data that feeds the pivot table
In order to update this report on a daily basis we would need the raw data provided for us every day and a report template. Let assume that the raw data has been updated for us every day and it is delivered in an excel file called Raw_Data.xlsx. the raw data may look like
We would also need a report template which should looks like the actual report Report_Template.xlsx
For now we have the following files and folders. The report should include all the data from Raw_data.xlsx and the pivot table from the Report_Template.xlsx
The Shared Folder is the place where our report should be uploaded so it would be accessible by the stakeholders. In order to create our new report we would need to
- Open the Raw_data.xlsx and copy the sheet "Raw" in Report_Template.xlsx.
- In Report_Template.xlsx we have to delete the sheet "Data" with the outdated data and rename the previously copied "Raw" sheet from Raw_data.xlsx to "Data".
- We have to change the pivot table data source in sheet "Report" so the pivot table would include all the data from sheet "Data".
- We have to refresh the pivot table to apply the new data.
These are pretty easy actions if you do this by hand, but if you have to do this for 10-15 reports per day then it gets annoying. It would be much better if all this has been done by the windows os and the ms office and you can go for a 5 mins refreshing walk in a meanwhile :) Lets start with the automation:
- Create a new excel file that would hold all the VB code. I will create one Macro-Enabled Excel file and will call it libSimpleVBAFunc.xlsm
- In this particular automation I am using very simple VBA functions library that I include in every excel automation project.
The Simple-VBA-Functions are available on GitHub. For more information about the Simple-VBA-functions you can refer directly to the GitHub Wiki and README file . The folder and files structure should look like this:
After I have downloaded the from the GitHub Simple-VBA-functions and I have imported the libSimpleVBAFunc.bas as module called libSimpleVBAFunc in the libSimpleVBAFunc.xlsm. You can visit this post http://blog.velingeorgiev.com/visual-basic-applications-automation-library-simple-vba-functions for more information on how to import the Simple-VBA-functions to your project. I have created additional custom module called "Main" which would hold all the automation code. The Excel VB Editor should look like this.
3. Open the Main module and create a Sub named Main() then we would add some pieces of code: I will split the code on PHASES so it would be easy to track what is happening PHASE 1 1. Some performance optimization
2. Defining the procedure variables
3. Adding a sheet that will represent a log and it will record centrain actions executed from the macro.
4. Assigning the Raw_Data.xlsx, Report_Template.xlsm full paths to a variable including the file exstension. Assigning the full path and name to the report that would be created to a variable. Out Report file is still not created, but by assigning this path to a variable at the start of the ececution will keep all files paths at one in the code and these variables will be used later in the code.
I have used custom function from the Simple-VBA-functions library to add a certan date in the name of the report. This will result in today - 1 day, because I am providing report with yestardays data. Now we have all the paths predefined and we can start with the execution. PHASE 2 - checks against the files avalability
PHASE 3 - updating the data.
PHASE 4 - saving the template as new report
PHASE 5 - zip the report
PHASE 6 - copy the report to the shared folder
PHASE 7 - you may want to include an email notification
PHASE 8 - closing all the excel instances
And this is pretty much the all the procedure that will open the excel files and would create our report then would zip it and copy it to the shared folder or send it as an MS Outlook mail. Now we have a running macro that will do all the work instead of us and we just need to schedule the Windows Task Scheduler to execute our macro on a daily basis. You need to turn off the macro locks in excel trust center to aloud the macro to be executed on excel workbook open. You need to call the Main sub on Workbook_Open to start the execution rihgt after the excel opens.
Then open the Windows Task Scheduler by typing "Task" in the Start menu - Search Window
Create a new task.
Add a title
On the Triggers tab schedule what time the excel file will be opened
On the New Action tab. "Start a program" should be selected in the Action dropdown. The full path of the excel macro file should be entered Program/script box .
This way the excel file will be opened by the Windows Task Scheduler and the code execution will begin on workbook open.
Thanks for your time!