Home>Blogs>VBA>Data Consolidation: Allow User to select files on Runtime
Data Consolidator
VBA

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?

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

 

Data Consolidation

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

Youtube.com/@PKAnExcelExpert

 

Watch step by step video tutorial:

.

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com