Thanks for the reply. There were some errors so I've adjusted the code as such: 



Sub M_snb()
    sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c dir ""G:\OF\*.xls*"" /b/s"), vbCrLf), ":")
     
    For j = 0 To UBound(sn())
        With GetObject(sn(j))
            sp = .Sheets(1).UsedRange
            .Close 0
        End With
         
        ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(sp), UBound(sp, 2)) = sp
    Next
End Sub
However, when I run the sub, it says run time error 429, activex component can't create object. sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c dir ""G:\OF\*.xls*"" /b/s"), vbCrLf), ":") is highlighted.

Moreover, am I supposed to run this sub after running the mergecode2?

In case it matters, I'm using excel 2011 on mac. Thanks!