How to read FileNames in a given Folder on MAC?

Question

My goal is to read the FileNames of all png files in a given folder.

I've Windows VBA code which uses the ActiveX FileSystemObject.

On a MAC This code results in

"runtime error 429 activex component can't create object"

Function ReadFileNames(ByVal sPath As String) As Integer
    
    Dim oFSO, oFolder, oFile As Object
    Dim sFileName As String
                        
    Set oFSO = CreateObject("scripting.FileSystemObject")
    Set oFolder = oFSO.getfolder(sPath)
            
    For Each oFile In oFolder.Files
        
        If Not oFile Is Nothing And Right(LCase(oFile.Name), 4) = ".png" Then  ' read only PNG-Files
            sFileName = oFile.Name
            ' do something with the FileName ...
        End If
        
    Next oFile
        
End Function

Answer 1

Here is a sub, using the native VBA DIR command, listing EXCEL workbooks in a folder by printing their names on the debug window:

Public Sub DirXlList()
    Const cstrPath As String = "c:\users\xxxx\misc\"
    Dim strDirItem As String
    strDirItem = Dir(cstrPath & "*.xlsx")
    While strDirItem <> ""
       Debug.Print "FileName: " & strDirItem, "FullPath: " & cstrPath & strDirItem
       strDirItem = Dir()
       DoEvents
    Wend
End Sub

Does this help? In

Update: doevents command allows Excel to process other pending user interface activities, such as window refreshes, mouse-clicks. If you have lots of files (thousands) in a folder, Excel may appear unresponsive/frozen in a loop like this. It is not necessary, as it will become responsive again, once it completes the loop. If you have only a few hundred files then it is an overkill. Remove and try.