Merge Excel files within a directory [Solved]

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Go back to All Questions Login or Register
Andie B 4 Rep.

I have almost no knowledge of VBA. Am using the code to merge all files in a directory and come up with the error in the attached image. Here is the code I am using. Can you tell me where I am going wrong?

Sub MergeExcelFiles(fileNames() As String, Optional worksheetName As String = vbNullString, Optional mergedFileName As String = “merged.xlsx”)
Dim fileName As Variant, wb As Workbook, ws As Worksheet, destWb As Workbook, excelApp As Application
Set excelApp = New Application
Set destWb = excelApp.Workbooks.Add

For Each fileName In fileNames
Set wb = excelApp.Workbooks.Open(fileName, ReadOnly = True)
For Each ws In wb.Sheets
If worksheetName vbNullString Then
If ws.Name = worksheetName Then ws.Copy After:=destWb.Sheets(destWb.Sheets.Count)
ws.Copy After:=destWb.Sheets(destWb.Sheets.Count)
End If
Next ws
wb.Close SaveChanges:=False
Next fileName

destWb.SaveAs ThisWorkbook.Path & “” & mergedFileName
destWb.Close SaveChanges:=False
Set destWb = Nothing: Set excelApp = Nothing
MsgBox “Merge completed!”
End Sub

Sub TestMergeDirectory()
Dim fileNames() As String, currIndex As Long, fileName As String, directory As String

directory = ThisWorkbook.Path & “SomeDir”
ReDim fileNames(0 To 0) As String
fileName = Dir(directory)
fileNames(0) = directory & fileName
Do Until fileName = vbNullString
currIndex = currIndex + 1
ReDim Preserve fileNames(0 To currIndex) As String
fileName = Dir
fileNames(currIndex) = directory & fileName
ReDim Preserve fileNames(0 To currIndex – 1) As String

MergeExcelFiles fileNames
End Sub


AnalystCave selected answer

Best answer

AnalystCave 376 Rep.

Hi Andie,

to make it easier please find a working example pack using this code:

AnalystCave selected as best answer


Simply the best place to learn VBA!

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match