Loop through Files in Folder on Mac

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


Sub Basic_Dir_Example_Mac()
'Ron de Bruin, 27-Feb-2019
'Only for Mac Excel 365 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.
Download MacGetFiles.zip
31-July-2021
Web design by Will Woodgate