Macro for Merging Excel Files in One Workbook with Overwriting

njpamdnc

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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
 

Attachments

  • screenshot.png
    screenshot.png
    201.6 KB · Views: 13

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,418
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.

VBA Code:
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
 
Last edited:
Solution

njpamdnc

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

njpamdnc

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

Attachments

  • siarq3(2).png
    siarq3(2).png
    187 KB · Views: 5
Last edited by a moderator:

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,418
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

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEF
1Study Island
2
3Assignment Report
4
5Class: Math CC 7 - McLeod - 2
6School: NORTHEAST INTERMEDIATE SCHOOL
7Program: 7th Grade - PA Standards Mastery
8Subject: Math (PA Core) - Edition 2
9Topic: Equivalent Expressions
10Assignment Date: 02/01/2021 Start of Day
11Due Date: 02/05/2021 End of Day
12Enforce Dates: No
13
14StudentSIS Primary KeyEquivalent ExpressionsRibbons
15(8 items & 70%)Earned
16SessionsBest SessionCumulative Score
17LastName, FirstName 01XXXXXXX0--0 out of 1 (0%)
18LastName, FirstName 02XXXXXXX112.5%12.5%0 out of 1 (0%)
19LastName, FirstName 03XXXXXXX0--0 out of 1 (0%)
20LastName, FirstName 04XXXXXXX150%50%0 out of 1 (0%)
21LastName, FirstName 05XXXXXXX0--0 out of 1 (0%)
22LastName, FirstName 06XXXXXXX0--0 out of 1 (0%)
23LastName, FirstName 07XXXXXXX0--0 out of 1 (0%)
24LastName, FirstName 08XXXXXXX187.5%87.5%1 out of 1 (100%)
25LastName, FirstName 09XXXXXXX362.5%37.5%0 out of 1 (0%)
26LastName, FirstName 10XXXXXXX187.5%87.5%1 out of 1 (100%)
27LastName, FirstName 11XXXXXXX287.5%75%1 out of 1 (100%)
28LastName, FirstName 12XXXXXXX187.5%87.5%1 out of 1 (100%)
29LastName, FirstName 13XXXXXXX150%50%0 out of 1 (0%)
30LastName, FirstName 14XXXXXXX187.5%87.5%1 out of 1 (100%)
31LastName, FirstName 15XXXXXXX0--0 out of 1 (0%)
32LastName, FirstName 16XXXXXXX175%75%1 out of 1 (100%)
33LastName, FirstName 17XXXXXXX125%25%0 out of 1 (0%)
34LastName, FirstName 18XXXXXXX0--0 out of 1 (0%)
35LastName, FirstName 19XXXXXXX125%25%0 out of 1 (0%)
36LastName, FirstName 20XXXXXXX187.5%87.5%1 out of 1 (100%)
37LastName, FirstName 21XXXXXXX2100%87.5%1 out of 1 (100%)
38LastName, FirstName 22XXXXXXX1100%100%1 out of 1 (100%)
39LastName, FirstName 23XXXXXXX762.5%42.8%0 out of 1 (0%)
40LastName, FirstName 24XXXXXXX125%25%0 out of 1 (0%)
41Total2756.9%9 out of 24 (38%)
42
43
44Footnotes
45Gradebook reports show all statistics for the specified date range and does not filter data using the start dates of individual students.
46Green numbers show the topics that have been passed and blue ribbons earned.
SIAR(00)
 

Attachments

  • siarq3(1).png
    siarq3(1).png
    188.4 KB · Views: 2
  • siarq3(2).png
    siarq3(2).png
    187 KB · Views: 2

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,418
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

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Attachments

  • siar(00).png
    siar(00).png
    187.8 KB · Views: 2

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,418
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,383
Messages
5,641,835
Members
417,240
Latest member
pjohnsonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top