Topics

Merge Multiple excel File in one File

Rumman Ansari 2022-09-13 Developer miscellaneous > Merge Multiple excel File in one File 436 Share

Example 1

In this blog I will show you how to add multiple excel file in one excel file in different sheets.

<span class="pln">
</span><span class="typ">Sub</span><span class="pln"> </span><span class="typ">GetSheets</span><span class="pun">()</span><span class="pln"> 
</span><span class="typ">Path</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"\" 
Filename = Dir(Path &amp; "</span><span class="pun">*.</span><span class="pln">xlsx</span><span class="str">") 
Do While Filename &lt;&gt; "" 
Workbooks.Open Filename:=Path &amp; Filename, ReadOnly:=True 
For Each Sheet In ActiveWorkbook.Sheets 
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close 
Filename = Dir() 
Loop 
End Sub
</span>

Example 2

<span class="pln">
</span><span class="typ">Sub</span><span class="pln"> </span><span class="typ">MergeExcelFiles</span><span class="pun">()</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> </span><span class="typ">Path</span><span class="pln"> </span><span class="typ">As</span><span class="pln"> </span><span class="typ">String</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> </span><span class="typ">Filename</span><span class="pln"> </span><span class="typ">As</span><span class="pln"> </span><span class="typ">String</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> wbSource </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Workbook</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> ws </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Worksheet</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> newSheet </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Worksheet</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> lastCol </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Long</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> lastRow </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Long</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> copyRange </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Range</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> isFirstFile </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Boolean</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> baseSheetName </span><span class="typ">As</span><span class="pln"> </span><span class="typ">String</span><span class="pln">
    </span><span class="typ">Dim</span><span class="pln"> sheetCounter </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Integer</span><span class="pln">
    isFirstFile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">True</span><span class="pln">

    </span><span class="str">' Set the path to the folder containing the Excel files
    Path = "C:\Your\Folder\Path\Here\"  '</span><span class="pln"> </span><span class="typ">Update</span><span class="pln"> </span><span class="kwd">this</span><span class="pln"> to your folder path
    
    </span><span class="str">' Get the first Excel file from the directory
    Filename = Dir(Path &amp; "*.xlsx")
    
    '</span><span class="pln"> </span><span class="typ">Loop</span><span class="pln"> through all </span><span class="typ">Excel</span><span class="pln"> files </span><span class="kwd">in</span><span class="pln"> the folder
    </span><span class="typ">Do</span><span class="pln"> </span><span class="typ">While</span><span class="pln"> </span><span class="typ">Filename</span><span class="pln"> </span><span class="pun">&lt;&gt;</span><span class="pln"> </span><span class="str">""</span><span class="pln">
        </span><span class="str">' Open the current Excel file
        Set wbSource = Workbooks.Open(Path &amp; Filename, ReadOnly:=True)
        
        '</span><span class="pln"> </span><span class="typ">Get</span><span class="pln"> the </span><span class="kwd">base</span><span class="pln"> sheet name </span><span class="kwd">from</span><span class="pln"> the file name </span><span class="pun">(</span><span class="pln">without extension</span><span class="pun">)</span><span class="pln">
        baseSheetName </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Left</span><span class="pun">(</span><span class="typ">Filename</span><span class="pun">,</span><span class="pln"> </span><span class="typ">InStrRev</span><span class="pun">(</span><span class="typ">Filename</span><span class="pun">,</span><span class="pln"> </span><span class="str">"."</span><span class="pun">)</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
        sheetCounter </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
        
        </span><span class="str">' Loop through each sheet in the opened workbook
        For Each ws In wbSource.Sheets
            '</span><span class="pln"> </span><span class="typ">Copy</span><span class="pln"> all columns </span><span class="kwd">from</span><span class="pln"> the sheets </span><span class="kwd">in</span><span class="pln"> the first file
            </span><span class="typ">If</span><span class="pln"> isFirstFile </span><span class="typ">Then</span><span class="pln">
                ws</span><span class="pun">.</span><span class="typ">Copy</span><span class="pln"> </span><span class="typ">After</span><span class="pun">:=</span><span class="typ">ThisWorkbook</span><span class="pun">.</span><span class="typ">Sheets</span><span class="pun">(</span><span class="typ">ThisWorkbook</span><span class="pun">.</span><span class="typ">Sheets</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">)</span><span class="pln">
                </span><span class="str">' Rename the sheet based on the file name
                ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = baseSheetName &amp; "_" &amp; ws.Name
            Else
                '</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> a </span><span class="kwd">new</span><span class="pln"> sheet </span><span class="kwd">in</span><span class="pln"> the destination workbook
                </span><span class="typ">Set</span><span class="pln"> newSheet </span><span class="pun">=</span><span class="pln"> </span><span class="typ">ThisWorkbook</span><span class="pun">.</span><span class="typ">Sheets</span><span class="pun">.</span><span class="typ">Add</span><span class="pun">(</span><span class="typ">After</span><span class="pun">:=</span><span class="typ">ThisWorkbook</span><span class="pun">.</span><span class="typ">Sheets</span><span class="pun">(</span><span class="typ">ThisWorkbook</span><span class="pun">.</span><span class="typ">Sheets</span><span class="pun">.</span><span class="typ">Count</span><span class="pun">))</span><span class="pln">

                </span><span class="str">' Find the last row and column in the source sheet
                lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
                lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                
                '</span><span class="pln"> </span><span class="typ">Copy</span><span class="pln"> only the columns </span><span class="kwd">from</span><span class="pln"> the second column onward
                </span><span class="typ">Set</span><span class="pln"> copyRange </span><span class="pun">=</span><span class="pln"> ws</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="pln">ws</span><span class="pun">.</span><span class="typ">Cells</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">),</span><span class="pln"> ws</span><span class="pun">.</span><span class="typ">Cells</span><span class="pun">(</span><span class="pln">lastRow</span><span class="pun">,</span><span class="pln"> lastCol</span><span class="pun">))</span><span class="pln">
                copyRange</span><span class="pun">.</span><span class="typ">Copy</span><span class="pln"> </span><span class="typ">Destination</span><span class="pun">:=</span><span class="pln">newSheet</span><span class="pun">.</span><span class="typ">Cells</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span><span class="pln">
                
                </span><span class="str">' Rename the new sheet based on the file name and sheet counter
                newSheet.Name = baseSheetName &amp; "_part_" &amp; sheetCounter
                sheetCounter = sheetCounter + 1
            End If
        Next ws
        
        '</span><span class="pln"> </span><span class="typ">Close</span><span class="pln"> the source workbook without saving
        wbSource</span><span class="pun">.</span><span class="typ">Close</span><span class="pln"> </span><span class="kwd">False</span><span class="pln">
        
        </span><span class="str">' Move to the next file
        Filename = Dir()
        '</span><span class="pln"> </span><span class="typ">Set</span><span class="pln"> isFirstFile to </span><span class="kwd">False</span><span class="pln"> after the first file </span><span class="kwd">is</span><span class="pln"> processed
        isFirstFile </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">False</span><span class="pln">
    </span><span class="typ">Loop</span><span class="pln">
    
    </span><span class="typ">MsgBox</span><span class="pln"> </span><span class="str">"Sheets have been merged into this workbook."</span><span class="pln">
</span><span class="typ">End</span><span class="pln"> </span><span class="typ">Sub</span><span class="pln">
</span>

How It Works

Naming the Sheets:
- For the first file, the sheet names will be the original sheet name prefixed with the Excel file name.
- For subsequent files, new sheets will be named based on the Excel file name, followed by "part" and a counter (e.g., "FileName_part_1").
Base Sheet Name:
- baseSheetName is derived from the filename of the Excel file (excluding the extension). This name is used as a prefix for the new sheet names.
Sheet Counter:
- sheetCounter is used to differentiate between multiple sheets copied from the same Excel file.

Steps to Use the Code

Insert the VBA Code:
- Open the VBA editor in Excel, insert a module, and paste the updated code.
Set the Path:
- Update the Path variable to point to the folder where your Excel files are stored.
Run the Macro:
- Press F5 or go to Run > Run Sub/UserForm to execute the macro.
Save the Workbook:
- After the macro completes, save your workbook. The sheets will be named according to the file they came from.

This will result in the merged workbook having sheets named based on the original Excel file names, allowing you to easily identify the source of each sheet.

Share this Post: