Data Compilation time reducing Using VBA

Data Compilation Time Reduction Using VBA

Most of the time data is gathered from various Sub-divisions, compiled by Divisions, then by Circle offices, Region offices, and finally by Mantralaya.

Google Sheets is a good option, but internet connectivity issues and large file sizes can sometimes create problems.

Since the format is generally kept the same, copy-paste seems like a simple solution — but as we all know, it often consumes a huge amount of time.

Here I have tried to automate this process using VBA.

Demo Video

Here is a short demonstration of how it works:

VBA Script

The following VBA code performs the automation shown in the video. It can be further modified as per your specific requirements.

Features of this script:

1. Opens all Excel files in a specified folder
2. Reads the required data from each file (currently Sheet 4, rows 1–50, columns 1–20)
3. Saves the data in memory (array)
4. Closes each file without saving changes
5. After processing all files, writes the collected data to the master workbook

Option Explicit Sub prepare_data() Dim i As Long, j As Long Dim n As Long Dim rows As Long, columns As Long Dim path As String Dim filename As String Dim Col(1 To 5000, 1 To 30) As Variant ' Adjust size if needed Dim sourceFileName(1 To 5000) As String Application.ScreenUpdating = False i = 0 ' Folder where source files are kept (create "Files" folder in same location as this workbook) path = ThisWorkbook.path & "\Files\" filename = Dir(path & "*.xls*") ' *.xls* catches both .xls & .xlsx Do While filename <> "" On Error Resume Next Workbooks.Open Filename:=path & filename, _ ReadOnly:=True, _ UpdateLinks:=0 If Err.Number <> 0 Then MsgBox "Could not open: " & filename Err.Clear GoTo NextFile End If ActiveWorkbook.UpdateLinks = False ' Read data from Sheet 4 (index 4), rows 1-50, columns 1-20 For rows = 1 To 50 i = i + 1 For columns = 1 To 20 Col(i, columns) = Sheets(4).Cells(rows, columns).Value Next columns Next rows sourceFileName(i) = filename Workbooks(filename).Close SaveChanges:=False NextFile: filename = Dir Loop ' Write all collected data to active sheet For n = 1 To i For columns = 1 To 20 Cells(n, columns).Value = Col(n, columns) Next columns Next n Application.ScreenUpdating = True MsgBox "Data compilation completed! " & vbCrLf & _ i & " rows collected from all files.", vbInformation End Sub

Note: Place all source Excel files in a sub-folder named "Files" in the same directory as this macro workbook.
You can adjust the sheet number, row/column ranges, and array sizes according to your actual data structure.

---

Deep-Dive: Performance & Memory Optimization Mechanics

In high-volume public works and governmental administrative workflows, compiling hundreds of operational workbooks using traditional cell-by-cell loops can introduce severe latency. Understanding the underlying Component Object Model (COM) interface dynamics helps explain why specific optimizations dramatically cut execution time.

1. Cell-by-Cell Access vs. Direct Memory Array Transfer

When executing Sheets(4).Cells(rows, columns).Value inside nested loops, Excel initiates a cross-process COM bridge call for every single cell operation. Reading 50 rows by 20 columns across 100 workbooks results in 100,000 separate COM calls, incurring significant CPU overhead.

By dumping an entire range directly into a 2D Variant array in one step, you reduce thousands of COM calls down to a single read operation per file:

' High-Speed Bulk Memory Transfer (1 COM Call per File) Dim SourceData As Variant SourceData = wbSource.Sheets(4).Range("A1:T50").Value ' SourceData is now a 1-based 2D array allocated directly in RAM

2. Disabling Application Hooks During Batch Processing

To maximize execution speed, suppress background Excel application engines while executing batch aggregation procedures:

Application Property	Default State	Optimized Execution State	Engine Impact
Application.ScreenUpdating	True	False	Eliminates video buffer redraws for every opened file.
Application.Calculation	xlCalculationAutomatic	xlCalculationManual	Prevents full workbook dependency tree recalculations upon opening files.
Application.EnableEvents	True	False	Blocks Workbook_Open and Worksheet_Change triggers embedded in sub-division files.
Application.DisplayAlerts	True	False	Suppresses prompt dialogs (e.g., format mismatches, clipboard queries, macro warnings).

Enterprise Hardening & Production-Ready Refactored VBA Code

While basic scripts work well in controlled environments, enterprise deployment across diverse workstations requires robust error handling, dynamic range detection, FileSystemObject (FSO) handling, and memory cleanup.

Key Enhancements in Enterprise Architecture:

• Dynamic Data Boundaries: Uses UsedRange or dynamic row finding instead of fixed 50-row bounds.
• Explicit Workbook References: Replaces ActiveWorkbook with qualified object variables to prevent target focus loss.
• Error Interception: Uses a structured `Try-Catch-Finally` pattern to ensure screen updating and automatic calculations are restored even if a runtime error occurs.
• Sheet Identification Safety: Resolves sheets by codename or explicit name rather than index (since sheet indexes change if users insert or reorder tabs).

Option Explicit '' ============================================================================== '' Module : Mod_DataAggregator_Enterprise '' Description: Robust, memory-optimized batch file aggregator. '' ============================================================================== Public Sub RunEnterpriseDataCompilation() Dim fso As Object Dim targetFolder As Object Dim fileObj As Object Dim wbMaster As Workbook Dim wsMaster As Worksheet Dim wbSource As Workbook Dim wsSource As Worksheet Dim folderPath As String Dim rawData As Variant Dim outputBuffer() As Variant Dim maxRows As Long, maxCols As Long Dim nextMasterRow As Long Dim fileCount As Long Dim r As Long, c As Long ' Environment State Saver Variables Dim origCalcMode As XlCalculation On Error GoTo ErrorHandler ' 1. Cache Environment and Apply Optimization Flags With Application origCalcMode = .Calculation .ScreenUpdating = False .DisplayAlerts = False .EnableEvents = False .Calculation = xlCalculationManual End With Set wbMaster = ThisWorkbook Set wsMaster = wbMaster.ActiveSheet folderPath = wbMaster.path & "\Files\" ' 2. FileSystemObject Initialization (Late Bound) Set fso = CreateObject("Scripting.FileSystemObject") If Not fso.FolderExists(folderPath) Then MsgBox "Target directory does not exist:" & vbCrLf & folderPath, vbCritical, "Directory Missing" GoTo CleanExit End If Set targetFolder = fso.GetFolder(folderPath) nextMasterRow = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row If nextMasterRow = 1 And wsMaster.Cells(1, 1).Value = "" Then nextMasterRow = 1 Else nextMasterRow = nextMasterRow + 1 End If ' 3. Iterate Files using FSO For Each fileObj In targetFolder.Files If (fso.GetExtensionName(fileObj.path) Like "xls*") And (Left(fileObj.Name, 2) <> "~$") Then Set wbSource = Workbooks.Open(Filename:=fileObj.path, ReadOnly:=True, UpdateLinks:=0) ' Validate Worksheets Count Safety If wbSource.Worksheets.Count >= 4 Then Set wsSource = wbSource.Worksheets(4) ' Read static block A1:T50 directly into memory rawData = wsSource.Range("A1:T50").Value maxRows = UBound(rawData, 1) maxCols = UBound(rawData, 2) ' Bulk write to Master Sheet in one memory operation per file wsMaster.Cells(nextMasterRow, 1).Resize(maxRows, maxCols).Value = rawData ' Optional: Track source file metadata in Column U wsMaster.Cells(nextMasterRow, maxCols + 1).Resize(maxRows, 1).Value = fileObj.Name nextMasterRow = nextMasterRow + maxRows fileCount = fileCount + 1 End If wbSource.Close SaveChanges:=False Set wbSource = Nothing End If Next fileObj ' 4. Success Notification MsgBox "Enterprise Compilation Finished Successfully!" & vbCrLf & _ "Files Processed: " & fileCount & vbCrLf & _ "Total Output Rows: " & (nextMasterRow - 1), vbInformation, "Execution Complete" CleanExit: ' 5. Environment Restoration (Crucial Finally Block) With Application .ScreenUpdating = True .DisplayAlerts = True .EnableEvents = True .Calculation = origCalcMode End With Set fso = Nothing Set targetFolder = Nothing Exit Sub ErrorHandler: MsgBox "Fatal Error encountered during aggregation!" & vbCrLf & _ "Error Code: " & Err.Number & vbCrLf & _ "Description: " & Err.Description, vbCritical, "Execution Failure" If Not wbSource Is Nothing Then wbSource.Close SaveChanges:=False End If Resume CleanExit End Sub

Modern Non-VBA Alternative: Power Query (M Code) Solution

While VBA provides robust programmatic control, Microsoft Power Query (Get & Transform Data) offers a zero-code maintenance alternative built directly into Excel 2016 and later versions. Power Query operates without macro security flags, streams data seamlessly, and automatically handles schema drift.

Why Consider Power Query for Folder Compilation?

• No Macro Security Blocks: Executes safely without requiring user trust settings for .xlsm files.
• Self-Healing Schema Handling: Automatically expands, trims, cleans, and converts data types on refresh.
• Automatic Path Parameterization: Directly targets a directory and updates all target tables upon pressing Ctrl + Alt + F5.

Production Power Query (M Language) Script

To implement this in Excel: Go to Data > Get Data > From Power Query Advanced Editor, and paste the following M Code:

let // 1. Target Directory Definition SourceFolder = Folder.Files("C:\YourFolderPath\Files"), // 2. Filter out systemic hidden temporary files (~$*) FilteredFiles = Table.SelectRows(SourceFolder, each Text.StartsWith([Name], "~$") = false and (Text.EndsWith([Extension], ".xlsx") or Text.EndsWith([Extension], ".xls")) ), // 3. Parse Excel Binary Content ParsedContent = Table.AddColumn(FilteredFiles, "WorkbookData", each Excel.Workbook([Content])), ExpandedWorkbook = Table.ExpandTableColumn(ParsedContent, "WorkbookData", {"Name", "Data", "Item", "Kind"}, {"SheetName", "Data", "Item", "Kind"}), // 4. Isolate 4th Worksheet or Specific Target Structural Item FilterSheetsOnly = Table.SelectRows(ExpandedWorkbook, each [Kind] = "Sheet"), // 5. Extract Specific Grid Matrix Range (A1:T50) SliceDataMatrix = Table.AddColumn(FilterSheetsOnly, "CustomRange", each Table.FirstN(Table.SelectColumns([Data], List.Transform({1..20}, each "Column" & Text.From(_))), 50)), // 6. Clean and Output Structured Compilation Matrix FinalColumns = Table.SelectColumns(SliceDataMatrix, {"Name", "CustomRange"}), ExpandedData = Table.ExpandTableColumn(FinalColumns, "CustomRange", List.Transform({1..20}, each "Column" & Text.From(_))) in ExpandedData

Governance, IT Security & System Deployment Requirements

Enterprise Macro Security Compliance Notice

Deploying VBA solutions across network environments requires strict alignment with organizational cybersecurity frameworks:

• Trusted Locations Configuration: Network shares hosting compilation workbooks must be added to Excel's Trusted Locations via File > Options > Trust Center > Trust Center Settings > Trusted Locations to bypass MOTW (Mark of the Web) runtime blocking.
• Digital Code Signing: For enterprise distribution, sign the VBA project using a Self-Signed or Certificate Authority (CA) issued PKI Digital Signature (via Tools > Digital Signature inside the VBE).
• Handling UNC vs Mapped Network Drives: Using relative local paths like ThisWorkbook.path can fail if workbooks are opened via cloud sync engines (e.g., OneDrive / SharePoint HTTP URLs). Use FSO or API wrappers to resolve true UNC paths (\\Server\Share\...) when compiling over network storage.