Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching for File Contents (Excel 2000)

    I have been asked to see if I can find a way to search through many, many files (thousands, if not tens of thousands) to identify which files contain certain formulas. So, for example, if I wanted to see which files have VLOOKUPS, is there a way I can search all the Excel files in folders to identify which ones contain said formula.

    Or perhaps there is a utility that might service this purpose.

    Any help at all will be GREATLY appreciated.

    Thanks,

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Searching for File Contents (Excel 2000)

    George, here's an outline of what you need to do HTH :

    Use the FileSearch object to generate a list of workbooks to process
    (search on the Lounge for examples of FileSearch)
    For each Workbook:
    Open the Workbook:
    For each Worksheet in the Workbook:
    Use the Find method to find Vlookup in a formula
    (see the help file example for Find to see how to find all)
    next Worksheet
    Next Workbook
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Searching for File Contents (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> George

    Ok 1000s and 10,000 files that is a huge bulk, so I would suggest that you go off beat a bit and hire a Computer Forensics Examiner to do the search for you. These types of examiners have software that is not really readily available to civilians that can help.

    On the off track that you have 10 years to finish this project, you could write some VBA code to:

    1) Open a workbook from the "pile"
    2) Check it for the value you are searching for
    3) If it has it move it in a particular folder, say "Target Files"
    4) If it does not have it move it to a "Searched" folder
    5) Close the workbook
    6) Loop and get the next workbook and start from step 1 again.

    Come to think about it, would you check The Spreadsheet Detective maybe this can do the VBA job for you...

    Let me know if you need further help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for File Contents (Excel 2000)

    Sam,

    Thanks for your suggestion. I'm finding this to be a somewhat daunting task, but breaking it down into manageable chunks definitely helps.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for File Contents (Excel 2000)

    Wassim,

    Thanks for your reply. I really like the website you so kindly suggested. I haven't broken down and bought the book yet, but I probably will this week.

    Regarding the daunting project: I can tell you with confidence that they will not pay for any type of outside help, so it's my project, do or die <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> I am slowly making progress, though, taking it a step at a time and searching for help in places like Woody's Lounge.

    Thanks again.

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Searching for File Contents (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> George

    You can find all the help you need here, and in the VBA lounge as well.

    As you said, divide and conquer is the way to go. Make sure you have enough space on a hard drive and with some logical file system you should be able to do the work.

    AFA the outside help, well management is either driving you to get the skills you need, or is making a mistake in not hiring someone to help you, and transfer some skills, and shorten your learning curve.

    We'll all ship in and help you as much as possible.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Searching for File Contents (Excel 2000)

    George, I wanted to work on this to hone my own skills, so here's a function that will return True if the workbook passed to it contains a VLOOKUP formula. (I didn't test it precisely to fit your situation, but it should be OK.)

    Function hasvlook(wkbWB As Workbook) As Boolean
    Dim wsWkSheet As Worksheet
    Dim rngHasFormula As Range, rngCell As Range
    hasvlook = False
    For Each wsWkSheet In wkbWB.Worksheets
    On Error Resume Next
    Set rngHasFormula = wsWkSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
    If Not rngHasFormula Is Nothing Then
    For Each rngCell In rngHasFormula
    If InStr(rngCell.Formula, "VLOOKUP") > 0 Then
    hasvlook = True
    ' MsgBox "Cell " & rngCell.Address & " includes a vlookup formula"
    Exit For ' found one, no need to keep looking
    End If
    Next rngCell
    End If
    If hasvlook = True Then Exit For ' found one, no need to look at remaining worksheets
    Next wsWkSheet
    End Function

    You should be able to use this after you grab and open each current workbook, then pass the WB (as an object, not as a name)as a reference to this function in the format:

    If hasvlookup(activeworkbook) then ... <do something with it or record the ActiveWorkbook.FullName or something>

    Hopefully others can tune the function for speed.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for File Contents (Excel 2000)

    George
    There are a lot of great suggestions so far. I thought I'd add one to the mix <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Over on the Word threads I've twice coded multifile lookups to look for a particular kind of content e.g. <post#=195780>post 195780</post#>. It would be easy enough to adapt for Excel. (Just ask) <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    The key additional feature over current posts is that it gathered all the found file names as Hyperlinks on a clean document.
    That in turn creates a 'quick' way to then process the output - and, with the links changing colour once dealt to, a visual marker as to which files you've attended to.
    (I've presumed you're looking them up for a reason ...)

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for File Contents (Excel 2000)

    The code below has not been tested, but it should search through all of the .xls files in the directory C:FilesToCheck and pop up a MsgBox for all that contain the VLOOKUP function in a formula.

    <pre>Public Sub LoadFiles()
    Dim strFileName As String, strPath As String
    Dim oWorksheet As Worksheet, oWorkBook As Workbook, oCell As Range
    Dim oCellsToCheck As Range
    Dim bVLFound As Boolean
    Application.ScreenUpdating = False
    strPath = "C:FilesToCheck"
    iFileNum = FreeFile
    strFileName = Dir(strPath & "*.xls")
    Do While strFileName <> ""
    bVLFound = False
    Set oWorkBook = Workbooks.Open(Filename:=strPath & strFileName)
    For Each oWorksheet In oWorkBook.Worksheets
    Set oCellsToCheck = Nothing
    On Error Resume Next
    Set oCellsToCheck = oWorksheet.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not oCellsToCheck Is Nothing Then
    For Each oCell In oCellsToCheck
    If InStr(oCell.Formula, "VLOOKUP") > 0 Then
    bVLFound = True
    Exit For
    End If
    Next oCell
    End If
    If bVLFound Then Exit For
    Next oWorksheet
    Application.DisplayAlerts = False
    oWorkBook.Close
    Application.DisplayAlerts = True
    If bVLFound Then
    MsgBox strPath & strFileName & " contains a VLOOKUP"
    End If
    strFileName = Dir()
    Loop
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for File Contents (Excel 2000)

    Hi John,

    I just read through your code with much interest. I really like your UDF and I'm going to test it today. It looks to me like it will be REALLY useful for a lot more than this project.

    I am amazed at the varied and creative responses I've gotten here. I can't think of a better way to learn this stuff.

    Thanks much for your continued interest and many thanks for your efforts. I'll let you know how your code does for me.

    Regards,

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for File Contents (Excel 2000)

    Hi Andrew,

    Thanks for your interest and your suggestion. I took a look at the post you suggested but I think I need to spend some more time taking a longer look. I agree that it would need some adaptation for my needs, but it looks like it's very close to what I'm doing in Excel. I will take a longer, more thorough look as soon as I can.

    The biggest reason I am doing this project is that my boss told me she wants it done <img src=/S/wink.gif border=0 alt=wink width=15 height=15> More seriously, I think they are trying to do a study here of how certain formulas and functions are being used, why I don't know. VLOOKUP is only a test formula; if I can come up with a viable approach (or I should say if the Woody's Lounge community can), I think they will be looking at other functions and formulas, but that's just a guess on my part.

    Thanks again for your suggestion. I will let you know how I am progressing with your code when I have the time to take a longer, more detailed look.

    Regards,

  12. #12
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for File Contents (Excel 2000)

    Legare,

    Thank you for your interest and your approach. I haven't had time yet to test it but I will try it today when I get a chance.

    I'll let you know how it works for me.

    Thanks again,

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Searching for File Contents (Excel 2000)

    With Legare's excellent code consider adding the following to help speed up the file reads:

    Dim intCalcSet As Integer, intUpdateLinkSet As Integer

    ... before the file open loop starts, put with Application.ScreenUpdating = False ...

    intCalcSet = Application.Calculation ' get current setting of Tools, Options, Calculation
    intUpdateLinkSet = Application.AskToUpdateLinks ' get current setting of
    ' Tools, Options, Update Remote References
    Application.AskToUpdateLinks = False ' turn off link updating
    Application.Calculation = xlCalculationManual 'set calc to manual

    and at the end of the code, reset those settings to where they were, put with Application.ScreenUpdating = True ...

    Application.AskToUpdateLinks = intUpdateLinkSet
    Application.Calculation = intCalcSet
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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