Consulting

Results 1 to 3 of 3

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

  1. #1
    VBAX Regular
    Joined
    Mar 2018
    Posts
    17
    Location

    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
    ReDim temp(0)
    in first function. The error is "Wrong number of dimensions"


  2. #2
    VBAX Regular
    Joined
    Mar 2018
    Posts
    17
    Location
    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

  3. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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/
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •