Merging all files in a folder ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
696
Hi,
I have several hundred .xlsx files with data I want merged into one file.

* Only .xlsx files
* Sheets("Sheet1")
* Data is in A:F starting at row 2

Best way to approach this? Files are all under 100kb and contain roughly 2000 rows in each

Appreciate any help
 
Last edited:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,442
Office Version
  1. 365
Platform
  1. Windows
In your data ranges, is column A always populated with data (for any row that has data)?
Just trying to determine which column we can look at to find the last row of data in each file.

Do you want each sheets data placed underneath the previous one copied?
Also, are there header/title rows that you do want repeated with each file?
If so, what rows are those in?
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
696
Hi @Joe4

Yes Column A is always populated.

I already have the header row in the merged file so don't need that copying from any of the files and yes want it all merged in a single sheet.

Example - file 1
A2:F47 > Rows 2

Next data will be added to A48

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,442
Office Version
  1. 365
Platform
  1. Windows
Will the "merged file" be the file with the macro code?
Or is there some other pre-existing file that we want to use, or should we be creating a new "merged file" each time from scratch?
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
696

ADVERTISEMENT

@Joe4 should have mentioned that sorry

Im wanting the macro to run separately so it can be called from anywhere.

The merged file already exists and for now it's located at
Code:
C:/Temp/Merged.xlsx

Was thinking you could initiate the file like this then continue:

Code:
Dim MergedFile as Workbook

MergedFile = Workbooks.Open("C:\Temp\Merged.xlsx")
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,442
Office Version
  1. 365
Platform
  1. Windows
I am sure that someone could come up with someone a little slicker, but this works:
Code:
Sub MyCombineMacro()

    Dim mFile As Workbook
    Dim fName As String
    Dim dPath As String
    Dim dFile As Workbook
    Dim lRowM As Long
    Dim lRowD As Long
    
'   Designate file path of data files
    dPath = "C:\Temp\Files\"
    
'   Make sure a slash appears at the end of dPath
    If Right(dPath, 1) <> "\" Then dPath = dPath + "\"
    
    Application.ScreenUpdating = False
    
'   Open merged file
    Set mFile = Workbooks.Open("C:\Temp\Merged.xlsx")
    
'   Loop through all data files in designated path
    fName = Dir(dPath & "*.xlsx")
    Do While fName <> ""
'       Find last row with data on merged file
        mFile.Activate
        lRowM = Cells(Rows.Count, "A").End(xlUp).Row
'       Open data file
        Set dFile = Workbooks.Open(dPath & fName)
'       Go to Sheet1 and find last row in column A
        Sheets("Sheet1").Activate
        lRowD = Cells(Rows.Count, "A").End(xlUp).Row
'       Copy data from data file to merge file
        Range("A2:F" & lRowD).Copy
        mFile.Activate
        Cells(lRowM + 1, "A").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Close data file
        dFile.Close False
'       Move to next file
        fName = Dir
    Loop

'   Save and close
    mFile.Activate
    ActiveWorkbook.Save
    mFile.Close False
    
    Application.ScreenUpdating = True
    
    MsgBox "Merging files complete"

End Sub
Just be sure to change the data file path line near the top of the code to suit your needs.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,442
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
696
@Joe4

Is it possible to move all the files to another directory after merging?

To ensure same ones aren't merged again
 

Watch MrExcel Video

Forum statistics

Threads
1,108,711
Messages
5,524,436
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top