Merge Excel files within a directory

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

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

Simply the best place to learn Excel VBA