How to Automate Excel Dashboard Quickly – Part 2

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)

1. CSV – “Macro will read the files from CSV folder”
2. Template – “Code will written in macro enabled excel file and will be placed in this folder”
3. Report – “Final output will be saved in Report folder”

Approach:

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:

1. Open the template
2. Make sure it has VBA code (from the previous “Part -I” post)
3. 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