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

We have multiple Excel files in a folder. We’ve created a folder named saledata in D:/ to hold the Excel files.

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

We want to extract this sales data from the Excel files and collect it into one single file in a different location.

Things We Should Learn First

We’ll use the FileSystemObject object to access the computer’s file system to get the files inside the folder.

We need to put the folder path correctly to get access to the Excel files in it. To get the folder location:

• Open the folder
• 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.

How to Write Code in the Visual Basic Editor

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

• Put your macro in the visual basic editor.

• Press F5 to run the code.

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

Part 1.1 – Collect the Extracted Data into 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. The created sheets are named according to the source file names (Day1, Day2….) with the extracted data in it.

Part 1.2 – Extract Data into a Single Worksheet

• 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

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

As every dataset in the files in the folder has a header, we’ll keep the header only for the first file.

• 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

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

We want to extract only the first two rows of data from each of the Excel files.

• Set the range as A1:E3 in the following macro.
• 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

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

Method 4 – Extract Data from Selected Files into One Workbook Using a Macro in Excel

We want to select and extract data only from three files in the folder.

• 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 Open.

• The macro extracts the 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 the Practice Workbook

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