2. Defining the procedure variables
Sub Main() '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 Dim fLog Dim fCopy
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
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 5 - zip the 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 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
'EMAIL PHASE' 'You may want to send an email with the attached report. Example:' fMail = Mail("[email protected]", "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' 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
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" ####' Call Main.Main End Sub
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
I have just tried e-mailing. Perfect! (Благодаря)
Thanks Velin. It is of great use. Also, please help in selecting cells from excel sheet report to be put in the mail body.
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.
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.
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.
what happens if the Excel file contains more than one macro. Is it possible to run just a specific macro when file is opened?
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"
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
I am not sure if it is possile. Unfortunately, I haven't done it.
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.
I am getting red lines in my code at 3plcaes.
2)saving as line i.e.
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
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.
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 :-)