Recursively search files for information contained in excel cell and return path

Question

Okedoke... I have an Excel spreadsheet with a filename in column A. The filenames listed in column A appear in one or more text files in one or more source directories.

I need Excel to search the text files recursively and return the path(s) of the file(s) that contain the filename specified in column A into column B. If more than one file go to column C etc.

The Excel sheet would be

__________________________________
__|______A___________|______B_____|
1 | filename.avi     |            |
2 | another_file.flv |            |

The text files to search would be in multiple directories under C:\WebDocs\ and are DokuWiki pages some are quite short, such as this page that would need to be returned

===== Problem Description =====
Reopen a closed bank reconciliation.

===== Solution =====
Demonstration of the tool box routine that allows reposting of the bank rec.

{{videos:bank_rec_reopen1006031511.flv|}}

===== Additional Information -cm =====
You may have noticed that in the video there is a number to the right of the bank account number. In this case it was a 0. That indicates department 0 which is all departments. You get the department 0 if you have all departments combined using the option in the bank set up called "One Bank for All Departments". If this setting is not checked then when you create your starting bank rec for each department you will get a 1 to the right of the bank rec for department 1 and so on. You should normally only have a 0, or have numbers 1 or greater. If you have both, then the method was changed after the initial bank rec was made. You just have to be aware of this as you move forward. As always backup before you make any changes.

There are some other pages though that are quite long that do not contain videos but would be in the directories being searched. Format is the same, plain text, ==== are place holders for headings may contain links to other pages/sites.

I did find an existing VBA script that sort of does what I need it to. It does not recurse and returns too much information, date/time stamp for instance, where all I need is the path.

Private Sub CommandButton1_Click()

Dim sh As Worksheet, rng As Range, lr As Long, fPath As String
Set sh = Sheets(1) 'Change to actual
lstRw = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
Set rng = sh.Range("A2:A" & lstRw)

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
fPath = .SelectedItems(1)
End With


If Right(fPath, 1) <> "\" Then
fPath = fPath & "\"
End If

fwb = Dir(fPath & "*.*")
x = 2
Do While fwb <> ""
For Each c In rng
If InStr(LCase(fwb), LCase(c.Value)) > 0 Then
Worksheets("Sheet2").Range("C" & x) = fwb
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fwb)
Worksheets("Sheet2").Range("D" & x) = f.DateLastModified
Worksheets("Sheet2").Range("B" & x) = f.Path
Worksheets("sheet2").Range("A" & x) = c.Value
Columns("A:D").AutoFit
Set fs = Nothing
Set f = Nothing
x = x + 1
End If
Next
fwb = Dir
Loop
Set sh = Nothing
Set rng = Nothing

Sheets(2).Activate

End Sub

My attempts at moification so far have generally resulted in a broken script and have thus led me here asking for help.

Thanks,
Simon

Answer 1

Downlaoded the win32 port of the GNU tool grep from http://gnuwin32.sourceforge.net/

Saved the list of video files into a plain text file instead of using a spreadsheet.

grep --file=C:\file_containing video_file_names.txt -R --include=*.txt C:\Path\To\Files >grep_output.txt

The information written to the grep_output.txt file looked like

C:\wiki_files\wiki\pages/my_bank_rec_page.txt:{{videos:bank_rec_reopen1006031511.flv|}}

So there was the path to the file containing the video name and the video name on one line.

Imported the grep_output.txt file into a new Excel workbook.

Used regular formulae to do the following

• Split Column A at the "/" to give the path in Column A and the page and video information in Column B
• Split the data in in Column B at the ":{{" characters leaving page name in Column B and video information in Column C
• Stripped the :{{ and |}} from the front and rear of the string in Column C

Answer 2

From my limited experience, it seems you'd want to perform 4 tasks.

1) Loop through Directories

2) Loop through files per directory (Good idea to keep the filename in a variable)

3) Test the text file for values. Would suggest clear a "scribble sheet", import the file, run a check. e.g.

    Sheets("YourScratchPatch").Select
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & yourpath & yourfile.txt, Destination:=Range("A1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

4) if values are found, write the file name variable to the index sheet.

I'm sure there should be better (arrays?) ways to do the comparison check as well, but it depends on what's inside the text file (i.e. just one file name?)

More info on the text file structure would be useful. Hope this helps.