none
Searching for files using VBA RRS feed

  • Question

  • Hi All,

    Trying to create a search spreadsheet using excel 2013 and the following VBA coding found here, but can't get it to work and my VBA knowledge is limited at best.

    I need to have the code search the following directory and all sub folders within: W:\JohnCrane\JBUC\GS_Reliability\GSTechDirectory for files containing a word entered into cell (B14), with all results being placed in cell C20 onwards (hyperlinked), when the search command button is clicked.

    here's the code I have found and modified with little luck.

    Public Sub TestListDir()
        Worksheets(1).Cells(2, 1).Activate
        Call listDir("W:\JohnCrane\JBUC\GS_Reliability\GSTechDirectory", 1)
    End Sub

    Public Sub listDir(strPath As String, lngSheet As Long)
    Dim strFn As String
    Dim strDirList() As String
    Dim lngArrayMax, x As Long
    lngArrayMax = 0
    strFn = Dir(strPath & "*.*", 23)
    While strFn <> ""
        If strFn <> "." And strFn <> ".." Then
            If (GetAttr(strPath & strFn) And vbDirectory) = vbDirectory Then
                lngArrayMax = lngArrayMax + 1
                ReDim Preserve strDirList(lngArrayMax)
                strDirList(lngArrayMax) = strPath & strFn & "\"
            Else
                ActiveCell.Value = strPath & strFn
                Worksheets(lngSheet).Cells(ActiveCell.Row + 1, 1).Activate
            End If
        End If
        strFn = Dir()
    Wend
    If lngArrayMax <> 0 Then
        For x = 1 To lngArrayMax
            Call listDir(strDirList(x), lngSheet)
        Next
    End If
    End Sub

    many thanks for any help you can give.

    Tuesday, July 11, 2017 6:44 AM

Answers

  • Ask your admin to install Agent Ransack, it's free for personal and commercial use.

    What you try to accomplish can't be done with just a few lines, you need a lot of code and experience.
    Just my FileSearch class has over 900 lines... and you need more!

    Andreas.


    • Edited by Andreas Killer Tuesday, July 11, 2017 10:20 AM
    • Marked as answer by Daz_1983 Thursday, July 13, 2017 7:54 AM
    Tuesday, July 11, 2017 10:19 AM
  • Consider this option.

    http://learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/

    Try to find all files in C:\Windows with that and my code and any other tool you like and compare the output, you'll see that all those small codes did not find all files and folders.

    Believe me, my code is the result from extensive investigations. .-)

    Anyway, that is not the point in this thread, the OP asks to search for files containing a word entered into cell (B14)...

    And IMHO that is not possible with just a few lines... Or do you disagree?

    Andreas.

    • Marked as answer by Daz_1983 Tuesday, July 18, 2017 8:20 AM
    Sunday, July 16, 2017 4:04 AM

All replies