how to search text string in multiple excel without opening

I have saved a one excel 3 months before but i don't know the name of that excel. only remember the contains and i have more than 200 excel is there any way to find that text string without opening all the excel.
Pratik ArdeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
Unless you have your Windows Search set up to index Excel file content, then no easy way.  Have you tried using Windows Search or checked it's options.

A small freeware tool that can search inside Excel files is below, might be worth a try.


Info on configuring Windows search to index file content...



»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pratik ArdeAuthor Commented:
thanks for the help
But there is any other way
0
Bill PrewCommented:
Well, technically this approach requires opening each Excel file, but it's automated rather than manual, so you could try the VBA code below, adjusting the folder path and search string as needed.

Searching Through Many Workbooks (Microsoft Excel)

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
                        Exit Do
                    Else
                        lRow = lRow + 1
                        .Cells(lRow, 1) = wbk.Name
                        .Cells(lRow, 2) = wks.Name
                        .Cells(lRow, 3) = rFound.Address
                        .Cells(lRow, 4) = rFound.Value
                    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

Open in new window


»bp
0
Neil FlemingConsultant and developerCommented:
Another approach is to examine the underlying XML content of the Excel files. As you may know all post-2007 (?) Excel files are secretly zipped up collections of XML files. The method below exploits that to examine the underlying xml by renaming the file, unzipping it to a temporary location, and renaming it back again.

Not sure if this is quicker or slower than Bill's approach above. But was interesting to build. On my machine it took 17 seconds to scan 42 files, including 7 files larger than 1mb and 3 larger than 7mb.

Basically, the code below does the following:
  1. Running routine "findit" starts the scan (in my code example, it looks for the word "Elephant" in a folder on my machine. So you need to adjust to desired target search.
  2. "Findit" calls routine qFind. This routine assumes you have a reference to Microsoft Scripting Runtime in Tools/References. If you prefer, you can change all the fso, folder and file variables to objects and create the fso as in Bill's example above
  3. qFind calls routine "Unzip" which renames an .xlsx file as a .zip file, unzips the contents to a "tmp" folder, and names the file back to its original name
  4. qFind then opens (as a text file) the file "sharedstrings.xml" which should contain the text from the excel file
  5. If the target string is found, it debug.prints the found file name. Obviously you can replace this action with something more meaningful, such as actually opening the target file in Excel.

Here is the code:
Option Explicit

Sub findit()
Dim vTime As Double, iCount As Long
'measure start time
vTime = Timer
'call qFind
iCount = qFind("c:\neil work\software\excel\expertsexchangequestions", "Elephant")
'report results and time taken
Debug.Print iCount & " files searched in " & Format(Timer - vTime, "0.000") & " seconds"
End Sub

Function qFind(sPath As String, sText As String)
Dim iFile As Long, iCount As Long
Dim fso As FileSystemObject, fFol As Folder, fTMP As Folder, fFil As File, fXML As File
Dim sLine As String
Dim iChar As Long, iCheck As Long, found As Boolean

On Error GoTo errortrap
iFile = FreeFile
'create filsystem object (assumes Microsoft Scripting Runtime reference in tools/references)
Set fso = New FileSystemObject
Set fFol = fso.GetFolder(sPath)

'loop through files looking for .xlsx (adjust if need to search .xlsm)
For Each fFil In fFol.Files
    If Right(fFil.Name, 4) = "xlsx" Then
    iCount = iCount + 1
    'call unzip routine below, copying XML contents of excel file to tmp folder
    UnZip fFol.Path & "\tmp", fFil.Path
    Set fTMP = fso.GetFolder(fFol.Path & "\tmp")
        'examine sharedstrings.xml file if it exists
        If fso.FileExists(fFol.Path & "\tmp\xl\sharedstrings.xml") Then
        Set fXML = fso.GetFile(fFol.Path & "\tmp\xl\sharedstrings.xml")
        Else
        GoTo errortrap
        End If
    'read contents of xml file
    Open fXML.Path For Input As #iFile
    Do
    Line Input #iFile, sLine
        'search for target string
        If InStr(sLine, sText) Then
        'output result in immediate window -- replace as desired
        'with code to report in Excel, or indeed just to open the file in Excel
        Debug.Print sText & " found in " & fFil.Path
        Exit Do
        End If
    Loop Until EOF(iFile)
    Close #iFile
errortrap:
    'delete the tmp folder
    If fso.FolderExists(fTMP) Then fTMP.Delete (True)
    End If
'move to next file
Next
'clean up
Set fso = Nothing
Set fFol = Nothing
Set fFil = Nothing
Set fTMP = Nothing
Set fXML = Nothing
qFind = iCount
End Function
 
Sub UnZip(sTargetPath As String, Fname As Variant)
Dim oApp As Object
Dim FileNameFolder As Variant
Dim fso As FileSystemObject, ff As File, fFolder As Folder, sExt As String
 
'add separator if needed
If Right(sTargetPath, 1) <> Application.PathSeparator Then
sTargetPath = sTargetPath & Application.PathSeparator
End If

FileNameFolder = sTargetPath

'using filesystemobject, rename xlsx file as .zip
Set fso = New FileSystemObject
Set ff = fso.GetFile(Fname)
sExt = Right(Fname, Len(Fname) - InStrRev(Fname, "."))
ff.Name = Replace(ff.Name, sExt, "zip")

'create tmp folder if required
If fso.FolderExists(sTargetPath) Then
Else
fso.CreateFolder sTargetPath
End If

Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(ff.Path).Items
'rename file back again
DoEvents
ff.Name = Replace(ff.Name, "zip", sExt)
Set oApp = Nothing
Set fso = Nothing
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.