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?
This 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 to consolidate the data automatically in Excel.
Watch our YouTube channel for more coding tips
Watch step by step video tutorial:
.
