Automating Excel Macros for Data Reporting with the INSYNCR Plugin

The solution is to decouple the data refresh process from human intervention. By wrapping the INSYNCR functionality in an automation script, we can leverage the Windows operating system to handle the execution.

The automation architecture consists of three layers:

1. The Excel Macro: A VBA script inside the workbook that instructs INSYNCR to pull fresh data.
2. The Trigger Script: A VBScript file that launches Excel and runs the macro.
3. The Schedule: A Windows Task Scheduler entry that determines when the update happens.

Here is the step-by-step guide to setting this up.

Step 1: Create the Update Macro

First, we need to tell Excel what to do once it’s open. We will write a Visual Basic for Applications (VBA) macro that triggers the INSYNCR refresh command and saves the file.

Note: The exact VBA command to trigger INSYNCR depends on the specific version of the plugin, but most follow a standard naming convention or allow you to record the action.

1. Open your reporting Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Insert a new Module (Insert > Module).
4. Paste the following code. (This code assumes INSYNCR exposes a RefreshAll method or hooks into the standard Excel refresh. If INSYNCR has a specific button, you can use the Macro Recorder to find the specific command name).

Sub AutoRunINSYNCRReport()
    ' --- Configuration Variables ---
    Dim reportFilePath As String
    reportFilePath = "C:\Reports\Weekly_Sales_Master.xlsm"

Dim exportPdfPath As String
exportPdfPath = “C:\Reports\Output\Executive_Summary_” & Format(Now(), “yyyy-mm-dd”) & “.pdf”
‘ ——————————-

Dim wb As Workbook
Dim ws As Worksheet

Application.ScreenUpdating = False
On Error GoTo ErrorHandler

‘ 1. Ensure the workbook is the active one (if running from Personal macro workbook)
‘ If this code is INSIDE the report file, utilize ThisWorkbook
Set wb = ThisWorkbook

‘ 2. Trigger the INSYNCR Refresh
‘ Option A: If INSYNCR uses standard Excel Data Connections
wb.RefreshAll

‘ Option B: If INSYNCR requires a specific add-in call (Example)
‘ Application.Run “INSYNCR.Connect.RefreshAll”

‘ Wait for connections to finish refreshing (Crucial for background queries)
DoEvents
Application.CalculateUntilAsyncQueriesDone

‘ 3. Optional: Export a specific sheet to PDF for distribution
Set ws = wb.Sheets(“Dashboard”)
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=exportPdfPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

‘ 4. Save the Excel file with the new data
wb.Save

‘ 5. Close the workbook
‘ Note: We usually let the VBScript handle the application quit,
‘ but closing the workbook here is good practice.
wb.Close SaveChanges:=True

Application.ScreenUpdating = True
Exit Sub

ErrorHandler:
‘ Log errors to a simple text file for troubleshooting
Dim fileNum As Integer
fileNum = FreeFile
Open “C:\Reports\Logs\ErrorLog.txt” For Append As #fileNum
Print #fileNum, “Error at ” & Now() & “: ” & Err.Description
Close #fileNum

Application.ScreenUpdating = True
‘ Force close to prevent hanging processes
If Not wb Is Nothing Then wb.Close SaveChanges:=False
End Sub

5. Important: Save your file as an Excel Macro-Enabled Workbook (.xlsm).

Step 2: The VBScript Bridge

Windows Task Scheduler cannot easily “click” a button inside Excel. We use a VBScript file as a bridge. This script initializes the Excel application invisible to the user, opens the file, and runs the macro we just created.

1. Open Notepad or a code editor.
2. Paste the following script:

' Define paths
Dim excelPath
excelPath = "C:\Reports\Weekly_Sales_Master.xlsm"

Dim macroName
macroName = “AutoRunINSYNCRReport”

‘ Create Excel Instance
Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)

‘ Keep Excel invisible to avoid interrupting user or screen flickering
objExcel.Visible = False
objExcel.DisplayAlerts = False

On Error Resume Next

‘ Open the Workbook
Dim objWorkbook
Set objWorkbook = objExcel.Workbooks.Open(excelPath)

If Err.Number <> 0 Then
‘ Simple error logging if file fails to open
Dim objFSO, logFile
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set logFile = objFSO.OpenTextFile(“C:\Reports\Logs\ScriptError.txt”, 8, True)
logFile.WriteLine “Failed to open workbook: ” & Now()
logFile.Close
Else
‘ Run the automation macro
objExcel.Run “‘” & objWorkbook.Name & “‘!” & macroName
End If

‘ Clean up
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing

3. Save this file as TriggerReport.vbs.

Step 3: Scheduling the Task

The final piece of the puzzle is the Windows Task Scheduler, which acts as the clockwork for your automation.

1. Press the Windows Key and search for Task Scheduler.
2. Select Create Task (not Basic Task, as we need more options).
3. General Tab:
4. • Name: “INSYNCR Reporting Automation”
   • Security Options: Select “Run whether user is logged on or not”. This is critical for server-side or early morning execution.
   • Check “Run with highest privileges”.
4. Triggers Tab:
5. • Click New.
   • Set the schedule (e.g., Weekly, Mondays at 6:00 AM).
5. Actions Tab:
6. • Click New.
   • Action: Start a program.
   • Program/script: wscript.exe (This is the Windows Script Host).
   • Add arguments: "C:\Reports\TriggerReport.vbs" (The path to your script from Step 2, in quotes).
6. Conditions Tab:
7. • Uncheck “Start the task only if the computer is on AC power” if you are running this on a laptop that might be unplugged, though servers are preferred.
7. Click OK and enter your Windows password to save the credentials.