Automated Excel report with Task Scheduler and Simple VBA functions

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 Velin Georgiev blog image The second sheet shows the raw data that feeds the pivot table Velin Georgiev blog image 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 Velin Georgiev blog image We would also need a report template which should looks like the actual report Report_Template.xlsx Velin Georgiev blog image 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 Velin Georgiev blog image 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: Velin Georgiev blog image 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. Velin Georgiev blog image 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 Velin Georgiev blog imageVelin Georgiev blog image Create a new task. Velin Georgiev blog image Add a title Velin Georgiev blog image On the Triggers tab schedule what time the excel file will be opened Velin Georgiev blog image 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 . Velin Georgiev blog image 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
Posted on

Tags: Excel, Automation, VBA, Visual Basic for Applications, Macro, Simple-VBA-Functions

Comments
anonymous user | 24 Oct 2014

thank you verymuch velin, i found it very useful for me, and expecting more of such kind of stuff from you. Thank you once again


anonymous user | 28 Mar 2015

I have just tried e-mailing. Perfect! (Благодаря)


anonymous user | 18 May 2015

Thanks Velin. It is of great use. Also, please help in selecting cells from excel sheet report to be put in the mail body.


Velin Georgiev | 18 May 2015

I can give you tips on this , but unfortunately I do not have spare time to do it right now.

You should be able to get the values from
the cells from the excel sheet and turn them in to HTML table string. Something like: "<table><tr><td>Cell1.value</td></tr></table>". Then this string can be used in email HTML body.

The link provided by you has similar approach.


anonymous user | 30 Oct 2015

Thanks for your post. I add excel sheet for automate mail but while open file in my mail id ,excel file is converting in encrypt format .I am not getting whether some problem in office s/w or i did wrong process in task scheduler.


Velin Georgiev | 30 Oct 2015

Task scheduler is responsible only for running the excel program and It should not be the reason.
Is the email text encrypted or the excel file itself?

Few options I could think of:
Have you encrypted your hard drive?
If the excel file is encrypted: Your antivirus program may do something. Try to open the attachment in the email from another PC and see whether there is still issue.
If some email text or excel data: There may be some ASCII , UTF-8 issues. If your language is not English, but Russian for example you may get to encoding issues if ASCII format is used.

I am not sure, but it is highly possible the VBA editor to use ASCII instead of UTF encoding.


bor87 | 17 Nov 2015

Hi,

what happens if the Excel file contains more than one macro. Is it possible to run just a specific macro when file is opened?


Velin Georgiev | 17 Nov 2015

It should be possible. I don't do VBA since 2011, but here is some more information for you to start https://support.microsoft.com/en-us/kb/108519 and http://www.mrexcel.com/forum/excel-questions/747548-how-call-another-macro-existing-visual-basic-applications-code.html and http://www.excelforum.com/excel-general/506718-calling-on-a-macro-within-a-macro.html.

I guess the correct syntax was something like: Call "YourMacroName"


anonymous user | 17 May 2016

Can you please let me know, what to add if I want get a pivot table in mail body from a excel present in same folder


Velin Georgiev | 17 May 2016

I am not sure if it is possile. Unfortunately, I haven't done it.


anonymous user | 17 May 2016

Thanks for quick response.

If not pivot table, can select cells from a excel sheet be put in the mail body.

I found a link but was not able to integrate in your code. I was giving runtime error 91.

http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

Please let me know if that could be integrated in your code


anonymous user | 06 Jun 2016

Hi, I am using your code.

But I am finding it difficult to remove 'application or object defined error 1004' in line "Sheets("Report").PivotTables("PivotTable1").PivotCache.SourceData = _
"Data!" & Sheets("Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)"

Please suggest changes.


anonymous user | 21 Dec 2016

Hi,

I am getting red lines in my code at 3plcaes.

1)Sheets("Report").PivotTables("PivotTable1").
2)saving as line i.e.
FileFormat:=50

3)fMail in mail alerting

all are syntax error though I copy paste.

Since everyone getting output it can be mistake on my side.
since new to this I would like to learn from you guys.


Thank you for nice tutorial


anonymous user | 03 Jan 2017

Thank you. THANK you. THANK YOU!

You just saved another desperate analyst.
It would take me months just to come up with the
"simple" functions you have.

I am sure I will use them regularly.

Thank you ... again.


Karim Masarweh | 10 Apr 2018

Thanks a million times for this post. I have lots of reporting I do with Excel and I always looked for ways on how to automate them more and more and more.
The introduction of Scheduled Task will help with that a lot!

One comment on the above:
Unless I missed something, when you wrote about the code for workbook_open event, you didn't mention that it must be pasted in the ThisWorkBook module of the Workbook. It won't work if it is in a Sheet Code Module or Standard Code Module.
Maybe you should add this note :-)

THANK YOUUUUUUUUUUUUU