Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

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

Only for O365 Excel 2016 and above with the latest updates

VBA Dir is fixed now in the latest updates if you use O365, you can use Dir now to loop through files in a folder and use wildcards to filter to only loop through the files you want. See the basic example below that create a new file with a list of all the files in the folder that you select when you run the code. See also the other option with macscript below this example.

Sub Basic_Dir_Example_Mac()
'Ron de Bruin, 27-Feb-2019
'Only for Mac Excel 2016 with the latest updates
    Dim MyPath As String, FilesInPath As String
    Dim Fnum As Long, MyFiles() As String
    Dim Nwb As Workbook

    On Error Resume Next
    MyPath = MacScript("return posix path of (choose folder with prompt ""Select the folder"") as string")
    If MyPath = "" Then Exit Sub
    On Error GoTo 0

    'Fill in the path\folder where the files are if you want
    'Disable the code above if you want to hardcode the folder
    'MyPath = "/Users/rondebruin/Desktop/TestFolder4"

    'Add a PathSeparator at the end if the user forget it
    If Right(MyPath, 1) <> Application.PathSeparator Then
        MyPath = MyPath & Application.PathSeparator
    End If

    'If there are no Excel files in the folder exit the sub
    'Note : See how i use the * wildcard character in this example
    'to get all the files that have a extension that start with xl
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    'Fill the array(myFiles) with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop

    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
    
        ' Add a new workbook to copy the list of files in
        Set Nwb = Workbooks.Add
        With Nwb.Sheets(1).Range("A1:D1")
            .Value = Array("Directory", "File Name", "Date/Time", "Size")
            .Font.Bold = True
        End With
        
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
          On Error Resume Next
           With Nwb.Sheets(1)
                .Cells(Fnum + 1, 1).Value = MyPath   'Column A
                .Cells(Fnum + 1, 2).Value = MyFiles(Fnum)   'Column B
                .Cells(Fnum + 1, 3).Value = FileDateTime(MyPath & MyFiles(Fnum))    'Column C
                .Cells(Fnum + 1, 4).Value = FileLen(MyPath & MyFiles(Fnum))    'Column D
            End With
            On Error GoTo 0
        Next Fnum
        
        Nwb.Sheets(1).Columns.AutoFit
    End If

End Sub

 

For Excel 2011 and 2016, see the other option above if you run 2016 and up.

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