Search for a file in folder and subfolders (mac version)

Hello everyone!


I'm trying to use the functions in the tutorial on this page on my version of Excel for mac (2019)


What I would like to be able to achieve is the same thing: I have file names in a column, I would like to search if they are present in a folder and its subfolders and if yes, report the path to the file.


I replaced the characters "" with "/" (used by mac).

Next the first error I get is on

Code:

---

ReDim temp(0)

---

in first function. The error is "Wrong number of dimensions"

:help

Ok, I menaged to make it to work
Problem is it works only for "1st level" subfolders

Main folder -> Subfolder

Can someone help me make it work for (at least) 2nd level subfolders too?

Mainfolder -> 1st lv. Subfolder -> 2nd lv. Subfolder -> Files

The code I have right now is this

Code:

---

Public temp() As String

---

Code:

---

Function ListFiles(FileName As String, FolderPath As String)
FileName = FileName & ".mp3"
Dim k As Long, i As Long
ReDim temp(2, 0)
If Right(FolderPath, 1) <> "/" Then
    FolderPath = FolderPath & "/"
End If
Recursive FileName, FolderPath
k = Range(Application.Caller.Address).Rows.Count
 
If k < UBound(temp, 2) Then
Else
    For i = UBound(temp, 2) To k
          ReDim Preserve temp(UBound(temp, 1), i)
            temp(0, i) = ""
            temp(1, i) = ""
            temp(2, i) = ""
    Next i
End If
ListFiles = Application.Transpose(temp)
ReDim temp(0)
End Function

---

Code:

---

Function Recursive(FileName As String, FolderPath As String)
Dim Value As String, Folders() As String
Dim Folder As Variant, a As Long
ReDim Folders(0)
If Right(FolderPath, 2) = "//" Then Exit Function
Value = Dir(FolderPath, vbDirectory)
Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If GetAttr(FolderPath & Value) = 16 Then
            Folders(UBound(Folders)) = Value
            ReDim Preserve Folders(UBound(Folders) + 1)
        Else
            If Value = FileName Then
                temp(0, UBound(temp, 2)) = FolderPath
                temp(1, UBound(temp, 2)) = Value
                temp(2, UBound(temp, 2)) = FileLen(FolderPath & Value)
                ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
            End If
        End If
    End If
    Value = Dir
Loop
For Each Folder In Folders
    Recursive FileName, FolderPath & Folder & "/"
Next Folder
End Function

---

altough i have an office for mac 2019 installed Mac, i am not familiar with mac vba.

perhaps below site may help:
https://macexcel.com/examples/filesa...efolderexists/