Dir on a Mac is not working the same as in Excel for Windows, you can not use a
Filter and wildcards on a Mac like in Excel for Windows like this to get all Excel
FilesInPath = Dir(folderpath & "*.xl*")
Or this to get only xlsm files
FilesInPath = Dir(folderpath & "*.xlsm")
With Dir in Windows you can also say that you want all xls files that
contains or start with or Ends with a certain text. But this all is not
working on a Mac, so I looked for a solution that I can use on my Mac.
In the Workbook that you can download you can find a macro that will add a list on the worksheet of all the files that match the conditions. To make it easy I use cell references in the function call in the workbook.
In the macro we call the GetFilesOnMacWithOrWithoutSubfolders
function like this to fill the MyFiles string.
Call GetFilesOnMacWithOrWithoutSubfolders(Level:=1, ExtChoice:=0, FileFilterOption:=0, FileNameFilterStr:="SearchString")
This are the four arguments that you can set in the function call :
'Level : 1= Only the files in the folder, 2 to ? levels of subfolders
'ExtChoice :0=(xls|xlsx|xlsm|xlsb), 1=xls , 2=xlsx, 3=xlsm, 4=xlsb, 5=csv, 6=txt, 7=all files, 8=(xlsx|xlsm|xlsb), 9=(csv|txt)
'FileFilterOption : 0=No Filter, 1=Begins, 2=Ends, 3=Contains
'FileNameFilterStr : Search string used when FileFilterOption = 1, 2 or 3
You see that you that you have a lot of options with this function and it is easy to add/change extensions.
Note: This function is only working in the Mac Excel versions 2011 and 2016
Download the example workbook
File date: 4-Dec-2016
For Mac Excel 2016 you can download a FileName Lister add-in on this page : FileName Lister Add-in for Excel 2016 for the Mac