Data Consolidation: Allow User to select files on Runtime

In this article, we will explain to you the Data Consolidation from multiple Excel files using Application.GetOpenFileName in VBA. You can easily allow the user to select multiple excel files at runtime to consolidate the data. It will consolidate data on the “Data” worksheet of your macro file.

What is Data Consolidation?

Data consolidation is a process of combining data from different excel files into a single Excel file. This is a common requirement in data analysis wherein we need to compile data from multiple excel files to derive insights and make informed decisions.

Consolidating Data in Excel

Consolidating data in Excel is a straightforward process that involves importing data from multiple sources and compiling it into a single worksheet. Excel provides several built-in features like Power Query, Power Pivot, and Consolidate that can help you consolidate data from multiple sources. However, if you want to automate this process using VBA, you can use the Application.GetOpenFileName method.

Using Application.GetOpenFileName in VBA

Application.GetOpenFileName is a built-in VBA function that allows the user to select files on runtime. This function displays a dialog box which enables the user to select one or more files, and returns the file path and name(s) as a variant. This variant can then be used to open the selected file(s) and import data into your Excel workbook.

Consolidating Data using VBA

To consolidate data from multiple Excel files using VBA, follow the steps below:

Step 1: Open a new Excel workbook and press Alt+F11 to open the VBA editor.
Step 2: Insert a new module by clicking on Insert > Module.
Step 3: Copy the code provided below and paste it into the new module.
Step 4: Save the macro-enabled workbook.
Step 5: Run the macro by clicking on Developer > Macros and selecting the Consolidate_Data macro.
Step 6: A dialog box will appear allowing the user to select the files to consolidate. Select the files you want to consolidate and click on Open.
Step 7: The macro will consolidate the data from the selected files and display it on the “Data” worksheet of your macro file.

Option Explicit

Sub Consolidate_Data()

Dim wb As Workbook
Dim sh As Worksheet

Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data")

dsh.UsedRange.Clear

Dim File_Name As Variant

File_Name = Application.GetOpenFileName("Excel Files (*.xlsx),*.xlsx, Text Files (*.txt),*.txt", 1, "Select Excel Files to Consolidate", , True)

Dim i As Integer

Dim lr As Long

For i = LBound(File_Name) To UBound(File_Name)

lr = dsh.Range("A" & Application.Rows.Count).End(xlUp).Row

Set wb = Workbooks.Open(File_Name(i))
Set sh = wb.Sheets(1)

sh.UsedRange.Copy dsh.Range("A" & lr + 1)

wb.Close False

Next i

'============= Formatting ==============

dsh.Range("1:1").Delete
dsh.UsedRange.AutoFilter 1, dsh.Range("A1").Value
dsh.Range("A2:A" & Application.Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
dsh.AutoFilterMode = False

With dsh.UsedRange
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.EntireRow.RowHeight = 15
.EntireColumn.ColumnWidth = 15
.Font.Name = "Calibri"
.Font.Size = 9
.Borders.LineStyle = xlHairline
End With

With dsh.Range("A1", dsh.Cells(1, Application.WorksheetFunction.CountA(dsh.Range("1:1"))))
.Font.Bold = True
.Interior.ColorIndex = 23
.Font.ColorIndex = 2
End With

End Sub

Conclusion

In this article, we have explained a VBA code to consolidate data from multiple Excel files using Application.GetOpenFileName. You can easily allow the users to select multiple excel files at runtime. The macro will consolidate data on the “Data” worksheet of your macro file. We hope this article helps you automate your data consolidation tasks in Excel.