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

[kiltro]

Hello everyone!


I'm trying to use the functions in the tutorial on this page (https://www.get-digital-help.com/search-for-a-file-in-folder-or-subfolders-vba/) 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
ReDim temp(0) in first function. The error is "Wrong number of dimensions"

:help

[kiltro]

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


Public temp() As String

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

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

[mancubus]

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/filesandfolders/checkfilefolderexists/