If you find this post little hard to understand please see my earlier post “How to Automate Excel Dashboard Quickly – Part 1”
In previous post I shared how we can automate the excel quickly in easy way. In this post, I will show you how you can automate the excel report / dashboard without opening excel file. We need the following items must have before moving forward:
I will keep the files in separate folders hence I would need 3 folders as follows: (I have kept these folders in “C:\ExcelAutomation” path, you can choose your own if you wish to)
- CSV – “Macro will read the files from CSV folder”
- Template – “Code will written in macro enabled excel file and will be placed in this folder”
- Report – “Final output will be saved in Report folder”
The approach I will use in this automation would be:
First, creating a macro enabled template embedding the code I shared in my previous post.
Second, creating a new file same as template from the template file and executing the macro after adding the couple of line at the start of the macro which will enable copying the template into new file.
Third, I will create a .vbs file which will execute the macro from the excel file. This code first will open the excel file and then will execute the code.
Creating macro enabled file:
- Open the template
- Make sure it has VBA code (from the previous “Part -I” post)
- Save as the template with macro enabled file (with extension “.xlsm”)
Modifying VBA code with couple of lines – Replicating the template into a new report everyday:
Add the following code in the macro
' Following code will create another file from the template Sheets("Customer Profitability").Select Sheets("Customer Profitability").Copy Application.Visible = False Dim sFilePath sFilePath = "D:\ExcelAutomation\reports\CustomerProfitabilityAnalysis-" + Format(Now, "MMddyyyy-hhmmS") + ".xlsx" ' Saving the newly created file to disk ActiveWorkbook.SaveAs Filename:=sFilePath, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False Range("A1").Select
Now, let’s create auto execute file (.vbs). The following code will open the template file from “C:\ExcelAutomation\Template\” location and activate the macro. Save the following code into “C:\ExcelAutomation\RunReport.vbs” file.
Option Explicit On Error Resume Next RunReport Sub RunReport() Dim xlApp Dim xlBook Dim sFilePath sFilePath = "D:\ExcelAutomation\Template" Set xlApp = CreateObject("Excel.Application") xlApp.Workbooks.Open sFilePath & "\Customer_profitability_analysis_template.xlsm" ' AutomateDashboard is the macro name xlApp.Run "AutomateDashboard" Set xlBook = Nothing Set xlApp = Nothing End Sub
So, That’s all. Comment me / Advise me if you find issues implementing the same. Please make sure that you read my previous post for the whole code. You can check my previous code here – How to Automate Excel Dashboard Quickly – Part 1