Loop through Files | Mac Excel Automation

Loop through Files in Folder on Mac

VBA Dir is fixed now in the latest updates if you use 2019 or higher or Microsoft 365, 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.

Sub Basic_Dir_Example_Mac()
'Ron de Bruin, 27-Feb-2019
'Only for Mac Excel 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

Example with MacScript for Excel 2011 and higher

Download this example workbook with and example that is working in Excel 2011 and higher that use a custom function that use AppleScript.
Download MacGetFiles.zip