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

Sub Main()

'Disabling some options for better performance during the macro execution'

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayAlerts = False

2. Defining the procedure variables

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

Dim fLog

Dim fCopy

3. Adding a sheet that will represent a log and it will record centrain actions executed from the macro.

'CREATE A LOG'

fLog = NewLog()

fLog = Log("The procedure has started")

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.

'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"

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

'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 3 - updating the data.

Workbooks(strReportTemplateName).Activate

'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'

Workbooks(strRawDataName).Activate

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)

Workbooks(strReportTemplateName).Activate

Sheets("Report").Activate

'Set new range to the pivot table'

Sheets("Report").PivotTables("PivotTable1").PivotCache.SourceData = _ "Data!" & Sheets("Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

fLog = Log("Pivot tables have been refreshed")

PHASE 4 - saving the template as new report

'Save the template(Report_Template.xlsx) as a new report Report.xlsb'

Workbooks(strReportTemplateName).Activate

If IsWbOpen(strReportName) Then

Workbooks(strReportName).Close False

If FileExists(strReport) Then Kill strReport

fLog = Log("An existing " & strReport & " has been deleted.")

End

If Workbooks(strReportTemplateName).SaveAs strReport, FileFormat:=50

fLog = Log("A new report has been created on " & strReport)

PHASE 5 - zip the report

'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 6 - copy the report to the shared folder

'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 7 - you may want to include an email notification

'EMAIL PHASE'

'You may want to send an email with the attached report. Example:'

fMail = Mail("velin.georgiev@xxxx.com", "A new report", "Dear Velin,The new report is ready.Best Regards", True, , , strZipPath)

fLog = Log("An email has been sent")

PHASE 8 - closing all the excel instances

'SAVE AND CLOSE EXCEL PHASE'

Workbooks(strReportName).Close

Workbooks(strRawDataName).Close False

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayAlerts = True

ThisWorkbook.Activate

'LOG A NOTE'

fLog = Log("The procedure has ended")

ThisWorkbook.Save

Application.Quit

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.

Private Sub Workbook_Open()

'#### To stop the macro you can use keyboard = "ESC" ####'

Call Main.Main

End Sub

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!

Github: https://github.com/VelinGeorgiev/Simple-VBA-functions