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
1. Open the Raw_data.xlsx and copy the sheet "Raw" in Report_Template.xlsx.
2. 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".
3. We have to change the pivot table data source in sheet "Report" so the pivot table would include all the data from sheet "Data".
4. 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:
1. 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
2. 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 on this link.
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://velingeorgiev.pro/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
'Disabling some options for better performance during the macro execution'
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
3. Adding a sheet that will represent a log and it will record centrain actions executed from the macro.
Dim strRootFolder As String
Dim strRawDataPath As String
Dim strReportTemplatePath As String
Dim strReport As String
Dim strRawDataName As String
Dim strReportTemplateName As String
Dim strReportName As String
Dim strZipName As String
Dim strZipPath As String
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.
'CREATE A LOG'
fLog = NewLog()
fLog = Log("The procedure has started")
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
'Setting up the files path'
strRootFolder = ThisWorkbook.Path & "\"
strRawDataPath = strRootFolder & "Raw_data.xlsx"
strReportTemplatePath = strRootFolder & "Report_Template.xlsx"
strReport = strRootFolder & "Report_" & CStr(GetDate("dd-mmm-yy", "d", -1)) & ".xlsb"
PHASE 3 - updating the data.
'Check if the files exist'
If FileExists(strRawDataPath) = False Then strRawDataPath = BrowseForFile("File not found. Please select the 'Raw_data.xlsx' file.")
If FileExists(strReportTemplatePath) = False Then strReportTemplatePath = BrowseForFile("File not found. Please select the 'Report_Template.xlsx' file.")
'Check if the workbooks are open'
If Not IsWbOpen(strRawDataPath) Then Workbooks.Open strRawDataPath
fLog = Log("File" & strRawDataPath & " has been opened in excel")
If Not IsWbOpen(strReportTemplatePath) Then Workbooks.Open strReportTemplatePath
fLog = Log("File" & strReportTemplatePath & " has been opened in excel")
'Get the names of the files'
strRawDataName = GetFileName(strRawDataPath)
strReportTemplateName = GetFileName(strReportTemplatePath)
strReportName = GetFileName(strReport)
PHASE 4 - saving the template as new report
'Check if the data sheet exist and deletes it. This sheet would be replaced by the new data from the Raw_Data.xlsx'
If SheetExists("Data") Then ActiveWorkbook.Sheets("Data").Delete
fLog = Log("Sheet Data has been deleted from " & strReportTemplateName)
'Copy the data from the data source file Raw_Data.xlsx'
If SheetExists("Raw") Then Sheets("Raw").Copy After:=Workbooks(strReportTemplateName).Sheets("Report")
ActiveSheet.Name = "Data"
fLog = Log("A new raw data has been copied from " & strRawDataName & " to " & strReportTemplateName)
'Set new range to the pivot table'
Sheets("Report").PivotTables("PivotTable1").PivotCache.SourceData = _ "Data!" & Sheets("Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
fLog = Log("Pivot tables have been refreshed")
PHASE 5 - zip the report
'Save the template(Report_Template.xlsx) as a new report Report.xlsb'
If IsWbOpen(strReportName) Then
If FileExists(strReport) Then Kill strReport
fLog = Log("An existing " & strReport & " has been deleted.")
If Workbooks(strReportTemplateName).SaveAs strReport, FileFormat:=50
fLog = Log("A new report has been created on " & strReport)
PHASE 6 - copy the report to the shared folder
'ZIP PHASE: Create a zip file'
strZipPath = Zip(strRootFolder, strReport, "Report_" & GetDate("mmm-yy", "m", -1))
fLog = Log("A Zip file has been created on " & strZipPath)
PHASE 7 - you may want to include an email notification
'COPY PHASE: Copy to the final destination'
fCopy = Copy(strZipPath, strRootFolder & "Shared Folder\")
'You may want to copy these files directly to a sharepoint. Example:'
'fCopy = Copy(strZipPath, "\\mySharePoint.com\mySite\Shared Documents\")'
fLog = Log("File " & strZipPath & "has been copied to " & strRootFolder & "FolderToCopyIn\")
PHASE 8 - closing all the excel instances
'You may want to send an email with the attached report. Example:'
fMail = Mail("email@example.com", "A new report", "Dear Velin,The new report is ready.Best Regards", True, , , strZipPath)
fLog = Log("An email has been sent")
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.
'SAVE AND CLOSE EXCEL PHASE'
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'LOG A NOTE'
fLog = Log("The procedure has ended")
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!
Available on Github
Private Sub Workbook_Open()
'#### To stop the macro you can use keyboard = "ESC" ####'