Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Loop through Files in Folder on a Mac (Dir for Mac Excel)

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 files
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

 

More Information

Note: Check out also the Merge code examples page and my RDBMerge add-in.

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