Searching for File Contents (Excel 2000)

Topic

New Reply

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,

Reply | Quote

Replies

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

Reply | Quote

Sam,

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

Reply | Quote

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

Reply | Quote

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 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.

Reply | Quote

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

Reply | Quote

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 …

Hopefully others can tune the function for speed.

Reply | Quote

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,

Reply | Quote

George
There are a lot of great suggestions so far. I thought I’d add one to the mix
Over on the Word threads I’ve twice coded multifile lookups to look for a particular kind of content e.g. post 195780. It would be easy enough to adapt for Excel. (Just ask)
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 …)

Reply | Quote

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 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,

Reply | Quote

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.

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

Reply | Quote

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,

Reply | Quote

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

Reply | Quote