Excel Macro: Extract Data from Multiple Excel Files (4 Methods)

In this article, we’ll illustrate how to extract data from multiple Excel files that are in a specific folder into a single workbook using Macro. The extracted data can be collected into single or multiple sheets of that workbook.

Extract Data from Multiple Excel Files Using a Macro: 4 Examples

Let’s say we have multiple Excel files in a folder in our computer storage. In this case, we’ve created a folder named saledata in the Local Disk D to hold the Excel files.

The Excel files contain sale data for 5 consecutive days for a fruit shop. For example, in the file named Day1.xlsx, we have the sale details of the products of the date 1/1/2020.

We want to extract these sale data from the Excel files of the folder and collect them into one single file that must be in another location other than this folder.

Things We Should Learn First

In our macro, we’ll use the FileSystemObject object to access our local computer’s file system to get the files inside the folder.

Then, we need to put the folder path correctly to get access to the Excel files in it. To get the folder location, we need to-

• Open the folder
• Then click on the address bar in the windows file explorer
• Copy the highlighted address with Ctrl + C.

We’ll use the  For Each loop to loop through all the Excel files inside the folder and the For Next Loop to extract data from the source files and paste them to the new file.

Write Code in Visual Basic Editor

To extract data from multiple Excel files to a new workbook, we’ll use several VBA functions and properties in our code. The following section describes how to open and write code in the visual basic editor.

• Right-click on the sheet name.
• Choose the View Code option.

• Now put your macro in the visual basic editor.

• Press F5 to run the code.

1. Run a Macro to Extract Data from Multiple Excel Files to a Single Workbook  

At first, we created a new Excel workbook to collect the extracted data from different files. As we mentioned earlier, the new workbook must be outside the folder that has the source Excel files.

1.1 Collect the Extracted Data into Different Worksheets

In this illustration, we’ll extract data from multiple Excel files and then collect them in different worksheets. Copy and paste the macro into the visual code editor and press F5 to run it.

Sub ExtractDataToDifferentSheets()
    On Error GoTo HandleError
    Application.ScreenUpdating = False
    Dim objectFlieSys As Object
    Dim objectGetFolder As Object
    Dim file As Object
    Set objectFlieSys = CreateObject("Scripting.FileSystemObject")
    Set objectGetFolder = objectFlieSys.GetFolder("D:\saledata")       ' The folder location of the source files.
    Dim counter As Integer
    counter = 1
    For Each file In objectGetFolder.Files
        Dim sourceFiles As Workbook
        Set sourceFiles = Workbooks.Open(file.Path, True, True)
        Dim rowsNumber As Integer
        rowsNumber = sourceFiles.Worksheets("sheet1").UsedRange.rows.Count
        Dim colsNumber As Integer
        colsNumber = sourceFiles.Worksheets("sheet1").UsedRange.Columns.Count
        Dim rows, cols As Integer
        For rows = 1 To rowsNumber
            For cols = 1 To colsNumber
                Application.Workbooks(1).ActiveSheet.Cells(rows, cols) = _
                       sourceFiles.Worksheets("Sheet1").Cells(rows, cols)
            Next cols
        Next rows
        rows = 0
        Dim worksheetName As String
        worksheetName = Replace(sourceFiles.Name, ".xlsx", "")
        sourceFiles.Close False
        Set sourceFiles = Nothing
        With ActiveWorkbook
            .ActiveSheet.Name = worksheetName
            counter = counter + 1
            If counter > .Worksheets.Count Then
                .Sheets.Add After:=.Worksheets(.Worksheets.Count)
            End If
            .Worksheets(counter).Activate
        End With
    Next
HandleError:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

We’ve successfully extracted and collected the data into 5 different sheets in the same workbook. We configured the code to create new sheets in the workbook according to the number of Excel files in the folder. And then the newly created sheets are named according to the source file names (Day1, Day2….) with the extracted data in it.

1.2 Extract Data into a Single Worksheet

In this illustration, we’ll extract and then collect data from multiple files into one single worksheet of the new workbook. To do that, copy, paste, and then run the following macro in the visual basic editor.

Sub ExtractDataToSingleFile()
    On Error GoTo HandleError
    Application.ScreenUpdating = False
    Dim objectFileSys As Object
    Dim objectGetFolder As Object
    Dim file As Object
    Set objectFileSys = CreateObject("Scripting.FileSystemObject")
    Set objectGetFolder = objectFileSys.GetFolder("D:\saledata") ' The folder location for the source files.
    Dim counter As Integer
    counter = 0
    For Each file In objectGetFolder.Files
        Dim sourceFiles As Workbook
        Set sourceFiles = Workbooks.Open(file.Path, True, True)
        Dim rowsNumber As Integer
        rowsNumber = sourceFiles.Worksheets("sheet1").UsedRange.rows.Count
        Dim colsNumber As Integer
        colsNumber = sourceFiles.Worksheets("sheet1").UsedRange.Columns.Count
        Dim rows, cols As Integer
        For rows = 1 To rowsNumber
            For cols = 1 To colsNumber
                Cells(rows + counter, cols) = sourceFiles.Worksheets("Sheet1").Cells(rows, cols)
            Next cols
        Next rows
        counter = counter + rows
        rows = 0
        sourceFiles.Close False
        Set sourceFiles = Nothing
    Next
HandleError:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Here is the output in the following screenshot.

Read More: How to Pull Data from Multiple Worksheets in Excel

2. Extract and Then Merge Data from Multiple Excel Files to a Single File Using a Marco

In this illustration, we’ll extract data from different files and merge them into one file. As every dataset in the files in the folder has a header, we’ll keep the header only for the first file. Let’s copy and paste the following macro into the visual basic editor.

Sub ExtractMergeDataFromMultipleFiles()
Dim i As Long
Dim currentRow As Long
Dim row As Long
Dim wBook As workBook
For i = 1 To 5 Step 1
Set wBook = Workbooks.Open("D:\saledata" & "\Day" & i & ".xlsx")
With wBook.Sheets("Sheet1")
If i = 1 Then
row = 1
Else
row = 2
End If
Do Until .Range("A" & row).Value = vbNullString
currentRow = currentRow + 1
For n = 0 To 4 Step 1
Me.Range("A" & currentRow).Offset(columnoffset:=n).Value = .Range("A" & row).Offset(columnoffset:=n).Value
Next n
row = row + 1
Loop
End With
wBook.Close True
Next i
Set wBook = Nothing
End Sub

The following screenshot shows the merged dataset.

Code Explanation:

In the code, there are two loops. The first For Next loop was set for 5 iterations as we have 5 files in the folder.

For i = 1 To 5 Step 1

And we set the second loop to iterate 5 times (n=0 to 4) as we have 5 columns in our dataset.  

For n = 0 To 4 Step 1

3. Set Range to Extract Data from Multiple Files to a Single Workbook Using a Macro in Excel

The following macro facilitates us to choose the range of data that we want to extract from the dataset into a new workbook. In this example, we want to extract only the first two rows of data from each of the Excel files.

To accomplish this, we need to set the range as A1:E3 in the following macro. Now copy and paste in the visual basic editor.

Sub ExtractDataMuilpleFiles()
    Dim location As String, files As String
    Dim eFiles() As String
    Dim rowCount As Long, fileNum As Long
    Dim wBook As Workbook, masterSheet As Worksheet
    Dim srcRng As Range, dstRng As Range
    Dim rowNum As Long, calType As Long
    ' Put the folder location
    location = "D:\saledata"
    ' This will put a Slash if necessary
    If Right(location, 1) <> "\" Then
        location = location & "\"
    End If
    files = Dir(location & "*.xl*")
    If files = "" Then
        MsgBox "Not Found"
        Exit Sub
    End If
    fileNum = 0
    Do While files <> ""
        fileNum = fileNum + 1
        ReDim Preserve eFiles(1 To fileNum)
        eFiles(fileNum) = files
        files = Dir()
    Loop
    With Application
        calType = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set masterSheet = ActiveWorkbook.ActiveSheet
    rowNum = 1
    If fileNum > 0 Then
        For fileNum = LBound(eFiles) To UBound(eFiles)
            Set wBook = Nothing
            On Error Resume Next
            Set wBook = Workbooks.Open(location & eFiles(fileNum))
            On Error GoTo 0
            If Not wBook Is Nothing Then
                On Error Resume Next
                ' Put the Range to Extract Data from
                With wBook.Worksheets(1)
                    Set srcRng = .Range("A1:E3")
                End With
                If Err.Number > 0 Then
                    Err.Clear
                    Set srcRng = Nothing
                Else
                    If srcRng.Columns.Count >= masterSheet.Columns.Count Then
                        Set srcRng = Nothing
                    End If
                End If
                On Error GoTo 0
                If Not srcRng Is Nothing Then
                    rowCount = srcRng.rows.Count
                    If rowNum + rowCount >= masterSheet.rows.Count Then
                        MsgBox "Not enough rows in target worksheet."
                        masterSheet.Columns.AutoFit
                        wBook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else 
                        Set dstRng = masterSheet.Range("A" & rowNum)
                        With srcRng
                            Set dstRng = dstRng. _
                                            Resize(.rows.Count, .Columns.Count)
                        End With
                        dstRng.Value = srcRng.Value
                        rowNum = rowNum + rowCount
                    End If
                End If
                wBook.Close savechanges:=False
            End If
        Next fileNum
        masterSheet.Columns.AutoFit
    End If
ExitTheSub:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = calType
    End With
End Sub

As the output, we’ve extracted only two rows of data from the dataset of each source files to the newly created worksheet.

Set your own data range in the following line of the macro.

 Set srcRng = .Range("A1:E3")

Read More: How to Pull Data from Multiple Worksheets in Excel VBA

4. Extract Data from Selected Files into One Workbook Using Macro in Excel

We’ve configured the previous macro so that we can choose Excel files from the file explorer. This way we can specify the source files to extract data from. Let’s say we want to select and extract data only from 1st 3 files in the folder. Let’s follow the steps below to accomplish this.

• Copy and paste the following macro into the visual basic editor.

Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
SetCurrentDirectoryA szPath
End Sub
Sub ExtractDataFromSelectedFiles()
Dim eLocation As String
Dim rowCount As Long, filesNum As Long
Dim wBook As Workbook, masterSheet As Worksheet
Dim srcRng As Range, dstRng As Range
Dim rowNum As Long, calType As Long
Dim saveLocation As String
Dim fileName As Variant
With Application
calType = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
saveLocation = CurDir
' Change this to the path\folder location of the files.
ChDirNet "D:\saledata"
fileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
If IsArray(fileName) Then
Set masterSheet = ActiveWorkbook.ActiveSheet
rowNum = 1
For filesNum = LBound(fileName) To UBound(fileName)
Set wBook = Nothing
On Error Resume Next
Set wBook = Workbooks.Open(fileName(filesNum))
On Error GoTo 0
If Not wBook Is Nothing Then
On Error Resume Next
With wBook.Worksheets(1)
Set srcRng = .Range("A1:E4")
End With
If Err.Number > 0 Then
Err.Clear
Set srcRng = Nothing
Else
If srcRng.Columns.Count >= masterSheet.Columns.Count Then
Set srcRng = Nothing
End If
End If
On Error GoTo 0
If Not srcRng Is Nothing Then
rowCount = srcRng.rows.Count
If rowNum + rowCount >= masterSheet.rows.Count Then
MsgBox "not enough rows in target worksheet."
masterSheet.Columns.AutoFit
wBook.Close savechanges:=False
GoTo ExitTheSub
Else
Set dstRng = masterSheet.Range("A" & rowNum)
With srcRng
Set dstRng = dstRng. _
Resize(.rows.Count, .Columns.Count)
End With
dstRng.Value = srcRng.Value
rowNum = rowNum + rowCount
End If
End If
wBook.Close savechanges:=False
End If
Next filesNum
masterSheet.Columns.AutoFit
End If
ExitTheSub:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = calType
End With
ChDirNet saveLocation
End Sub

• Run the macro by pressing F5.
• Go to the folder location in the file explorer.
• Select the desired files.
• Click the Open

• The macro successfully extracted data to the new worksheet.

Here in the code, we set the range as A1:E4 i.e., the whole dataset from the source files to extract and collect as output.

Things to Remember

All the macros we used in the above examples search data in the worksheet named “Sheet1” i.e., sheet number 1 from the source workbook, and then extract them to the new workbook.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Now, we know how to extract data from multiple Excel files into one workbook using macro with different examples. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Pull Values from Another Worksheet in Excel
• Pull Same Cell from Multiple Sheets into Master Column in Excel
• Extract Data from One Sheet to Another Using VBA in Excel
• How to Get Data from Another Sheet Based on Cell Value in Excel
• How to Pull Data From Another Sheet Based on Criteria in Excel

<< Go Back To Extract Data Excel | Learn Excel