Macro for Merging Excel Files in One Workbook with Overwriting

[njpamdnc]

Mr. Excel...My name is Robert, and I am a middle school math teacher who wants to merge multiple Excel files into a single workbook. Furthermore, I need the macro to replace/overwrite current files/worksheet tabs with new files/worksheet tabs that have the same name. I have read several similar posts in the message forum. However, I couldn't find information regarding replacing/overwriting. The following macro works terrific with the exception of not replacing/overwriting old data with new data:

Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

If (vbBoolean <> VarType(fnameList)) Then

If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wbkCurBook = ActiveWorkbook

For Each fnameCurFile In fnameList
countFiles = countFiles + 1

Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next

wbkSrcBook.Close SaveChanges:=False

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If

Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub

 

[smozgur]

Hi Robert,

If I get it correctly, you want to delete any existing worksheet and copy the one that is existing in the selected worksheets.

In that case, please try the following code. I modified the existing code as I would write it, so I changed some methods that it used above (it is perfectly fine to use those methods, it's just my preference as a programmer, i.e. using the IsArray method instead of using VarType and comparing with a boolean).

Caution: This code will delete worksheets in the active workbook if there is a new version coming with the selected workbooks. Therefore, please make sure you have a backup of your active workbook.

Note: There is one requirement to avoid a possible error - the active workbook should have more than one worksheet. The reason is that if it has only one worksheet named SheetA and it is the first sheet in the first opened file, then it is supposed to be deleted to be replaced with the new SheetA. In this rare situation, you'll get an error. To avoid that, you can either create a dummy worksheet in the active workbook or have the macro create a temporary worksheet to avoid it. As I said, it is a very small chance, but it might happen. If that makes you concerned and need extra code for that, then please let me know.

Another note: I am sure you understand that both Workbook1 and Workbook2 have SheetC (a worksheet with the same name), then the second one will overwrite the first one that was merged already.

Sub MergeExcelFiles()

Dim fnameList  As Variant, fnameCurFile As Variant, fname As Variant
Dim countFiles As Integer, countSheets As Integer
Dim wksCurSheet As Worksheet, wksCurBookSheet As Worksheet
Dim wbkCurBook  As Workbook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

' Check if user selected any file or clicked cancel
' At least one selected file will set fNameList as an array
If IsArray(fnameList) Then
    Set wbkCurBook = ActiveWorkbook
    ' No need to incremenet countFiles to find the total processed files
    ' it is the selected file count
    countFiles = UBound(fnameList)
   
    With Application
        .ScreenUpdating = False
        ' Setting DisplayAlerts as false to avoid sheet delete confirmation in case an overwrite
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
   
    For Each fnameCurFile In fnameList
        Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
        ' Get the total worksheet count instead of incrementing the variable
        countSheets = countSheets + wbkSrcBook.Worksheets.Count
        For Each wksCurSheet In wbkSrcBook.Sheets
            For Each wksCurBookSheet In wbkCurBook.Worksheets
                If wksCurBookSheet.Name = wksCurSheet.Name Then
                    ' We are overwritting an existing sheet
                    ' so, decrement the sheet counter
                    countSheets = countSheets - 1
                    wksCurBookSheet.Delete
                End If
            Next wksCurBookSheet
            wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Worksheets.Count)
        Next wksCurSheet
        wbkSrcBook.Close False
    Next fnameCurFile

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
   
    MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
Else
    MsgBox "No files selected", Title:="Merge Excel files"
End If

End Sub

 

[njpamdnc]

Hi Robert,

If I get it correctly, you want to delete any existing worksheet and copy the one that is existing in the selected worksheets.

In that case, please try the following code. I modified the existing code as I would write it, so I changed some methods that it used above (it is perfectly fine to use those methods, it's just my preference as a programmer, i.e. using the IsArray method instead of using VarType and comparing with a boolean).

Caution: This code will delete worksheets in the active workbook if there is a new version coming with the selected workbooks. Therefore, please make sure you have a backup of your active workbook.

Note: There is one requirement to avoid a possible error - the active workbook should have more than one worksheet. The reason is that if it has only one worksheet named SheetA and it is the first sheet in the first opened file, then it is supposed to be deleted to be replaced with the new SheetA. In this rare situation, you'll get an error. To avoid that, you can either create a dummy worksheet in the active workbook or have the macro create a temporary worksheet to avoid it. As I said, it is a very small chance, but it might happen. If that makes you concerned and need extra code for that, then please let me know.

Another note: I am sure you understand that both Workbook1 and Workbook2 have SheetC (a worksheet with the same name), then the second one will overwrite the first one that was merged already.

Sub MergeExcelFiles()

Dim fnameList  As Variant, fnameCurFile As Variant, fname As Variant
Dim countFiles As Integer, countSheets As Integer
Dim wksCurSheet As Worksheet, wksCurBookSheet As Worksheet
Dim wbkCurBook  As Workbook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

' Check if user selected any file or clicked cancel
' At least one selected file will set fNameList as an array
If IsArray(fnameList) Then
    Set wbkCurBook = ActiveWorkbook
    ' No need to incremenet countFiles to find the total processed files
    ' it is the selected file count
    countFiles = UBound(fnameList)
  
    With Application
        .ScreenUpdating = False
        ' Setting DisplayAlerts as false to avoid sheet delete confirmation in case an overwrite
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
  
    For Each fnameCurFile In fnameList
        Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
        ' Get the total worksheet count instead of incrementing the variable
        countSheets = countSheets + wbkSrcBook.Worksheets.Count
        For Each wksCurSheet In wbkSrcBook.Sheets
            For Each wksCurBookSheet In wbkCurBook.Worksheets
                If wksCurBookSheet.Name = wksCurSheet.Name Then
                    ' We are overwritting an existing sheet
                    ' so, decrement the sheet counter
                    countSheets = countSheets - 1
                    wksCurBookSheet.Delete
                End If
            Next wksCurBookSheet
            wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Worksheets.Count)
        Next wksCurSheet
        wbkSrcBook.Close False
    Next fnameCurFile

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
  
    MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
Else
    MsgBox "No files selected", Title:="Merge Excel files"
End If

End Sub

smozgur...So far so good. Thank you very much!!!...If I encounter any problem(s) with the code in the near future, I will let you know.

Robert

 

[smozgur]

You're welcome, Robert.

Glad to hear it helpsl

 

[njpamdnc]

smozgur...Your macro modification to replace current file tabs works well. However, I believe it created two problems I am hoping you can correct for me.

Problem #1 is the new imported files are displayed at the end of the tab list. My tab list starts with SIARQ1, SIARQ2, SIARQ3, SIARQ4. Each of these tabs shows student assignment data for each of the four quarters of a school year. The tab list continues with SIAR(00), SIAR(01), SIAR(02), SIAR(03)...up to SIAR(44) for a total of 45 tabs. Each of these tabs shows a report for a specific assignment for a specific class of students. There are 9 weeks in a school quarter. Therefore, SIAR(00), SIAR(01), SIAR(02), SIAR(03), SIAR(04), SIAR(05), SIAR(06), SIAR(07), SIAR(08) will be a quarter's worth of assignment reports data for one class. I have 5 classes of students. Therefore, I will theoretically have 45 assignment reports by the end of a school year. I typically reach about 4 or 5 assignments in a quarter. Therefore, I created extra tabs in the event I need them. I need the new imported files to be displayed immediately after the SIARQ4 tab.

Problem 2 is the links I created for the SIAR(00), SIAR(01), SIAR(02), SIAR(03) and SIARQ3 get broken after I run the macro. I don't know why Excel doesn't simply use the new files with the same exact names as the old files to prevent the links from breaking regardless of the tab order. Furthermore, I already enabled Excel to automatically update links before I ran the macro.

I have attached two picture files for your convenience. If you can help me fix these to problems, I will most appreciative.

Robert

 

[smozgur]

Another caution for the code I provided - If there are cells referenced in the deleted worksheets, then they will fail after deleting the referenced worksheets. I ignored this fact by looking at your sample "image" and assumed the worksheets are independent.

In your case, in my opinion, the best route would be to use predefined worksheets in the main workbook and transferring the data instead of the whole matching worksheet. You need the predefined worksheets to create the functions in the SIARQX worksheets anyway. Then you can define the data entry range in each separate workbook, and the cell values in those ranges could be transferred as values into the corresponding worksheets in the main workbook. This way, the structure won't be affected but the values will be updated only.

If you could send a SIAR(XX) mini-sheet as a sample by using XL2BB (without sensitive data please - I removed the image that contains real student names in your previous post), then we can see the worksheet structure, and try to suggest a more appropriate solution for the case.

 

[njpamdnc]

I uploaded a new picture file to comply with protecting sensitive data. I only use for the following cells and cell ranges from each SIAR(XX) tab to populate each SIARQX tab: Column A(names), Column D(best scores), A5 AND A9(filters). Furthermore, I use each of my SIARQX tabs to hold a school year's worth of assignment reports for all 5 of my classes. If you need a mini-sheet of a SIARQX, don't hesitate to let me know. I wish Mr. Excel allowed video chatting as another communication option...LOL

si excel reports files merger(mrexcel).xlsm
	A	B	C	D	E	F
1	Study Island
2
3	Assignment Report
4
5	Class: Math CC 7 - McLeod - 2
6	School: NORTHEAST INTERMEDIATE SCHOOL
7	Program: 7th Grade - PA Standards Mastery
8	Subject: Math (PA Core) - Edition 2
9	Topic: Equivalent Expressions
10	Assignment Date: 02/01/2021 Start of Day
11	Due Date: 02/05/2021 End of Day
12	Enforce Dates: No
13
14	Student	SIS Primary Key	Equivalent Expressions			Ribbons
15			(8 items & 70%)			Earned
16			Sessions	Best Session	Cumulative Score
17	LastName, FirstName 01	XXXXXXX	0	-	-	0 out of 1 (0%)
18	LastName, FirstName 02	XXXXXXX	1	12.5%	12.5%	0 out of 1 (0%)
19	LastName, FirstName 03	XXXXXXX	0	-	-	0 out of 1 (0%)
20	LastName, FirstName 04	XXXXXXX	1	50%	50%	0 out of 1 (0%)
21	LastName, FirstName 05	XXXXXXX	0	-	-	0 out of 1 (0%)
22	LastName, FirstName 06	XXXXXXX	0	-	-	0 out of 1 (0%)
23	LastName, FirstName 07	XXXXXXX	0	-	-	0 out of 1 (0%)
24	LastName, FirstName 08	XXXXXXX	1	87.5%	87.5%	1 out of 1 (100%)
25	LastName, FirstName 09	XXXXXXX	3	62.5%	37.5%	0 out of 1 (0%)
26	LastName, FirstName 10	XXXXXXX	1	87.5%	87.5%	1 out of 1 (100%)
27	LastName, FirstName 11	XXXXXXX	2	87.5%	75%	1 out of 1 (100%)
28	LastName, FirstName 12	XXXXXXX	1	87.5%	87.5%	1 out of 1 (100%)
29	LastName, FirstName 13	XXXXXXX	1	50%	50%	0 out of 1 (0%)
30	LastName, FirstName 14	XXXXXXX	1	87.5%	87.5%	1 out of 1 (100%)
31	LastName, FirstName 15	XXXXXXX	0	-	-	0 out of 1 (0%)
32	LastName, FirstName 16	XXXXXXX	1	75%	75%	1 out of 1 (100%)
33	LastName, FirstName 17	XXXXXXX	1	25%	25%	0 out of 1 (0%)
34	LastName, FirstName 18	XXXXXXX	0	-	-	0 out of 1 (0%)
35	LastName, FirstName 19	XXXXXXX	1	25%	25%	0 out of 1 (0%)
36	LastName, FirstName 20	XXXXXXX	1	87.5%	87.5%	1 out of 1 (100%)
37	LastName, FirstName 21	XXXXXXX	2	100%	87.5%	1 out of 1 (100%)
38	LastName, FirstName 22	XXXXXXX	1	100%	100%	1 out of 1 (100%)
39	LastName, FirstName 23	XXXXXXX	7	62.5%	42.8%	0 out of 1 (0%)
40	LastName, FirstName 24	XXXXXXX	1	25%	25%	0 out of 1 (0%)
41	Total		27		56.9%	9 out of 24 (38%)
42
43
44	Footnotes
45	Gradebook reports show all statistics for the specified date range and does not filter data using the start dates of individual students.
46	Green numbers show the topics that have been passed and blue ribbons earned.
SIAR(00)

 

[smozgur]

SIAR(XX) worksheets are function free? No functions in the SIAR(XX) worksheets?

All SIAR(XX) worksheets have Total cell in A1? (Trying to understand if you created fixed number of rows in all SIAR(XX) worksheets even less students exists in the class)

 

[njpamdnc]

My Responses to Your Questions

SIAR(XX) worksheets are function free? No functions in the SIAR(XX) worksheets? YES...ALL SIAR(XX) WORKSHEETS ARE FUNCTION FREE DUE TO THE FACT OF BEING THE ACTUAL DOWNLOADED ASSIGNMENT REPORTS. THE ONLY WORKSHEETS THAT CONTAIN FUNCTIONS ARE SIARQX.

All SIAR(XX) worksheets have Total cell in A1? ALL SIAR(XX) WORKSHEETS HAVE "Study Island" in cell A1 as shown in attachment.(Trying to understand if you created fixed number of rows in all SIAR(XX) worksheets even less students exists in the class) NO...ONLY SIARQX WORKSHEETS HAVE BEEN CREATED WITH FIXED NUMBER OF ROWS(40 ROWS) REGARDLESS OF NUMBER OF STUDENTS IN EACH CLASS.

smozgur...I had dream last night that caused me to get only 4 hours of sleep because I was extremely excited about experimenting with a basic Excel function(Find and Replace). I discovered a manual solution to prevent link-breaking by simply having Excel find all "=", and replacing them with "#" within the SIARQ3 worksheet. I ran your modified macro, and had Excel reverse the first step by finding all "#", and replacing them back to "=". This process works!!! However, I want importing new files, replacing current files and preventing link breakage to be automated to the fullest extent. If you can add another modification to the original macro that incorporates the manual steps I used with [1) "Find and Replace"; 2) Import New/Replace Current(Old); 3) Reverse "Find and Replace"] for all SIARQX tabs; 4) Displaying new file tabs immediately after the SIARQ4 tab, I'll be very satisfied.

I would like for you to continue working on your idea because I am very intrigued about it. Furthermore, your idea might be exactly what I want without me realizing it at the moment. If your idea doesn't work for whatever reason, I am requesting for you to use my idea as a backup.

 

[smozgur]

It was supposed to be A41 for the Total cell address inquiry, I had mistyped it. However, I can see that each SIAR(XX) worksheet might contain a different number of rows.

There are no formulas in the SIAR(XX) worksheets, so it might be very easy to deal with updating the values. I was actually just trying to make sure if you have all SIAR(XX) worksheets already in the main workbook. Since you have SIARQX worksheets that are supposed to be referring to the SIAR(XX) worksheets with formulas, I believe you should have all SIAR(XX) worksheets in the main workbook.

Could you please try the following macro. Instead of delete/copy worksheets, this one transfer the "values" of the used range of the worksheets. Although I believe all SIAR(XX) worksheets already exist in the main workbook, I still set the macro to transfer if a SIAR(XX) is missing in the main workbook.

This version should solve problem #2 with the formulas since we are not deleting any referenced worksheet anymore but instead updating the cell values in the existing worksheets.
However, about problem #1, we are not solving that part yet. We can deal with that if this new version works as you need. My recommendation would be creating all SIAR(XX) worksheets in the main workbook as the easiest workaround, but it is also possible to copy the worksheets in a certain order with some more coding.

Sub MergeExcelFiles()

Dim fnameList  As Variant, fnameCurFile As Variant, fname As Variant
Dim countFiles As Integer, countSheets As Integer
Dim wksCurSheet As Worksheet, wksCurBookSheet As Worksheet
Dim wbkCurBook  As Workbook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

' Check if user selected any file or clicked cancel
' At least one selected file will set fNameList as an array
If IsArray(fnameList) Then
    Set wbkCurBook = ActiveWorkbook
    ' No need to incremenet countFiles to find the total processed files
    ' it is the selected file count
    countFiles = UBound(fnameList)
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    For Each fnameCurFile In fnameList
        Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
        ' Get the total worksheet count instead of incrementing the variable
        countSheets = countSheets + wbkSrcBook.Worksheets.Count
        For Each wksCurSheet In wbkSrcBook.Worksheets
            On Error Resume Next
            wbkCurBook.Worksheets(wksCurSheet.Name).Cells(1, 1).Resize(wksCurSheet.UsedRange.Rows.Count, wksCurSheet.UsedRange.Columns.Count).Value = wksCurSheet.UsedRange.Cells.Value
            If Err Then
                wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Worksheets.Count)
                On Error GoTo 0
            End If
        Next wksCurSheet
        wbkSrcBook.Close False
    Next fnameCurFile

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
Else
    MsgBox "No files selected", Title:="Merge Excel files"
End If

End Sub