# How to merge multiple sheets of Excel files to one Excel file?

I need to merge hundred Excel files into one Excel file. Each Excel file needs to be merged into its own worksheet in the workbook and the destination worksheet needs to have the name of the original file (minus the extension). Is this possible?

## 1 Answer

Well, it doesn't seem like you've put any effort into this, but since I already have these macros written, I'll supply them for anyone else that is searching. These were written in excel 2007 and were part of a larger process.

It is important to note that this will fail if any of your file names have over 31 characters, excel has a character limit for sheet names

## Combine the files into one with the name of the worksheets being set to the file name -

Sub CombineWSs()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

MyPath = "C:\Documents and Settings\path\to"
Set wbDst = ThisWorkbook
strFilename = Dir(MyPath & "\*.xls", vbNormal)

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Loop
wbDst.Worksheets(1).Delete

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


## Now iterate through the sheets to remove the last five characters of the sheet name: .xlsx

Sub RenameWS()
Application.ScreenUpdating = False
Dim strName As String
Dim intLength As Integer

For Each Sheet In ActiveWorkbook.Worksheets
strName = Sheet.Name
intLength = Len(strName)
strName = Left(strName, intLength - 5)
Sheet.Name = strName

Next
Application.ScreenUpdating = True
End Sub

• Crude, but quite effective! – Tsaukpaetra Mar 18 '15 at 15:29
• When I run this, the sheets are named not by the filename, but rather by the sheets' name that's being copied. – dockeryZ Feb 6 '17 at 22:31

## protected by Community♦Apr 26 at 19:43

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?