Velin Georgiev Blog

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

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

    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.

    '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 4 - saving the template as new report

    'Save the template(Report_Template.xlsx) as a new report Report.xlsb'
    If IsWbOpen(strReportName) Then
        Workbooks(strReportName).Close False
        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 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, "\\\mySite\Shared Documents\")'
    fLog = Log("File " & strZipPath & "has been copied to " & strRootFolder & "FolderToCopyIn\")

PHASE 7 - you may want to include an email notification

    'You may want to send an email with the attached report. Example:'
    fMail = Mail("", "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

    Workbooks(strRawDataName).Close False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    'LOG A NOTE'
    fLog = Log("The procedure has ended")

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!