Results 1 to 1 of 1

Search multiple workbooks for string - better visualisation

Threaded View

  1. #1
    Registered User
    Join Date
    04-16-2021
    Location
    Pernik, Bulgaria
    MS-Off Ver
    Microsoft Excel 2013
    Posts
    1

    Smile Search multiple workbooks for string - better visualisation

    Dear all,

    I have these several huge excel files, each with a lot of sheets. I have to manually search each of them for specific string and to check if it exists somewhere.

    I have found the following code and so far it works. But in order to search for a string, every time I have to open the code and change the path and the search word:

    'Change as desired
    strPath = "c:\MyFolder"
    strSearch = "Specific text"


    Instead of opening the code and changing the path each timе, I would like somehow to have a search field or something like this on the picture attached. Is it possible?

    5KHw6.png

    Or for example two cells on the sheet with the results, where I can put the path and the search word, without opening the code?

    Sub SearchFolders()
    Dim fso As Object
    Dim fld As Object
    Dim strSearch As String
    Dim strPath As String
    Dim strFile As String
    Dim wOut As Worksheet
    Dim wbk As Workbook
    Dim wks As Worksheet
    Dim lRow As Long
    Dim rFound As Range
    Dim strFirstAddress As String
    
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    
    'Change as desired
    strPath = "c:\MyFolder"
    strSearch = "Specific text"
    
    Set wOut = Worksheets.Add
    lRow = 1
    With wOut
        .Cells(lRow, 1) = "Workbook"
        .Cells(lRow, 2) = "Worksheet"
        .Cells(lRow, 3) = "Cell"
        .Cells(lRow, 4) = "Text in Cell"
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fld = fso.GetFolder(strPath)
    
        strFile = Dir(strPath & "\*.xls*")
        Do While strFile <> ""
            Set wbk = Workbooks.Open _
              (Filename:=strPath & "\" & strFile, _
              UpdateLinks:=0, _
              ReadOnly:=True, _
              AddToMRU:=False)
    
            For Each wks In wbk.Worksheets
                Set rFound = wks.UsedRange.Find(strSearch)
                If Not rFound Is Nothing Then
                    strFirstAddress = rFound.Address
                End If
                Do
                    If rFound Is Nothing Then
                         lRow = lRow + 1
                        .Cells(lRow, 1) = wbk.Name
                        .Cells(lRow, 2) = wks.Name
                        .Cells(lRow, 3) = rFound.Address
                        .Cells(lRow, 4) = rFound.Value
    Else
    Exit Do
    
                    End If
                    Set rFound = wks.Cells.FindNext(After:=rFound)
                Loop While strFirstAddress <> rFound.Address
            Next
    
                wbk.Close (False)
                strFile = Dir
            Loop
            .Columns("A:D").EntireColumn.AutoFit
        End With
        MsgBox "Done"
    
    ExitHandler:
        Set wOut = Nothing
        Set wks = Nothing
        Set wbk = Nothing
        Set fld = Nothing
        Set fso = Nothing
        Application.ScreenUpdating = True
        Exit Sub
    
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub
    I hope my inquiry is understandable.

    I would be very grateful for the help.

    Rali
    Last edited by ralitsa.b; 04-16-2021 at 02:37 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Live Search MULTIPLE fields SIMULTANEOUSLY [search as you type]
    By deannaed in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-04-2018, 02:06 PM
  2. Replies: 11
    Last Post: 11-03-2013, 09:16 PM
  3. Search for a cell value in multiple workbooks and return a range
    By dompainter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2013, 09:27 AM
  4. Search for multiple fields / columns
    By paulmac66 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-07-2013, 04:47 AM
  5. Search a column across multiple workbooks and return the value of a different cell.
    By MrSpiffdifilous in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-11-2013, 10:42 AM
  6. Replies: 5
    Last Post: 03-29-2012, 04:12 AM
  7. Dedicated cell for search
    By Nigel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2007, 08:32 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1