In the example workbook that you can download below you find three macro examples and some tips how to change the code to fit your needs in Excel 2011 and higher. Note: I use my Custom Dir function with AppleScript in the example workbook to get the file names that I want.

Note: If you use Excel 2019 or higher or Microsoft 365 with the latest updates you can also use VBA Dir now to loop through the files in a folder, check out the page with the custom Dir function above for more information and also a code example too loop through the files in a folder with Dir.

Download Example workbook
File Date : 9-Dec-2019

I will add an example workbook soon that is doing the same with VBA Dir.

You can also try the Add-in I create on this page : Merge Add-in for Excel 2016 or higher for the Mac