'VBA on a MAC - How to read FileNames in a given Folder (have to avoid using the Windows ActiveX FileSystemObject)

I've got a Windows VBA code which I'd like to translate for a MAC, as there's no access to the ActiveX FileSystemObject.

My goal is to read all the FileNames of all PNG-Files in a given Folder. This Code runs well on windows, but results in a "runtime error 429 activex component can't create object" on a MAC:

    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

Can anyone translate this code for a MAC ?

vbamacosfilenamesactivexfilesystemobject


Solution 1:[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.

Solution 2:[2]

VBA for Mac can link to the entire c standard library, like this example:

Private Declare PtrSafe Function CopyMemory_byPtr Lib "libc.dylib" Alias "memmove" (ByVal dest As LongPtr, ByVal src As LongPtr, ByVal size As Long) As LongPtr

I'm too lazy to write out relevant examples for you, but if, by chance, you are familiar with using the c standard library for file manipulation, you can just do it that way.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 david

Related Questions