Merging all files in a folder ?

[JumboCactuar]

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

 

[Joe4]

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]

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]

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]

@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

C:/Temp/Merged.xlsx

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

Dim MergedFile as Workbook

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

 

[Joe4]

I am sure that someone could come up with someone a little slicker, but this works:

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.

 

[JumboCactuar]

@Joe4 it works perfect thankyou

 

[Joe4]

You are welcome.

 

[JumboCactuar]

@Joe4

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

To ensure same ones aren't merged again

 

[Joe4]

See here for code on how to do that: https://www.mrexcel.com/forum/excel-questions/637606-move-files-another-folder-vba.html