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?


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()


    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

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?

Not the answer you're looking for? Browse other questions tagged or ask your own question.