none
How to search all files in folders/subfolders for one or more strings? RRS feed

  • Question

  • I have a list of files (full path and file name) in Column D of my worksheet.  I'm trying to figure out how to run some code to open each file in this array (starting on D14 and going down), search for one or more strings, and then put an "X" in Column I, in the same row as the file name.

    How can I do that?

    I experimented with a few samples of code.  It seems pretty easy to do it in one folder, but not all sub folders.  The folder/subfolder is setup a specific way for a specific purpose.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, April 24, 2015 12:07 PM

Answers

  • Hi ryguy72,

    To iterate the folders and files, please refer to this code below:

    Dim FileSystem As Object
    Dim HostFolder As String
    
    HostFolder = "C:\"
    
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    
    Sub DoFolder(Folder)
        Dim SubFolder
        For Each SubFolder In Folder.SubFolders
            DoFolder SubFolder
        Next
        Dim File
        For Each File In Folder.Files
            ' Operate on each file
        Next
    End Sub

    Then you could check file name and open it. If it is the excel file, we could use Workbooks.Open method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ryguy72 Monday, April 27, 2015 1:15 PM
    Monday, April 27, 2015 7:47 AM
    Moderator
  • Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    

    This very simple solution works only partially, try list all files in C:\WINDOWS in an Excel sheet.

    Then compare the number of files you get with that code with the Windows Explorer, my code and a 3rd party tool, e.g. Agent Ransack.

    You will be amazed how big the differences are. ;-)

    Andreas.

    • Marked as answer by ryguy72 Monday, April 27, 2015 1:15 PM
    Monday, April 27, 2015 10:13 AM

All replies

  • Below is an example that allow you to search a directory and its subdirs txt files(you can change this), read its lines and search for specific word:

    using System;
    using System.IO;
    using System.Linq;
    
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var files = from file in Directory.EnumerateFiles(@"c:\", "*.txt", SearchOption.AllDirectories)
                            from line in File.ReadLines(file)
                            where line.Contains("Microsoft")
                            select new
                            {
                                File = file,
                                Line = line
                            };
    
                foreach (var f in files)
                {
                    Console.WriteLine("{0}\t{1}", f.File, f.Line);
                }
    			Console.WriteLine("{0} files found.", files.Count().ToString());
            }
            catch (UnauthorizedAccessException UAEx)
            {
                Console.WriteLine(UAEx.Message);
            }
            catch (PathTooLongException PathEx)
            {
                Console.WriteLine(PathEx.Message);
            }
        }
    }
    https://msdn.microsoft.com/en-us/library/dd997370%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396


    Fouad Roumieh

    Friday, April 24, 2015 12:22 PM
  • Can I get a sample in VBA, not C#.

    Thanks.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, April 24, 2015 1:32 PM
  • It seems pretty easy to do it in one folder, but not all sub folders.  The folder/subfolder is setup a specific way for a specific purpose.

    That is correct, search for files is more difficult as most people think.

    Download this file and import it into your VBA editor, a sample how to use is included in the comments of the class module.

    https://dl.dropboxusercontent.com/u/35239054/FileSearch.cls

    Andreas.

    Sunday, April 26, 2015 7:34 AM
  • Hi ryguy72,

    To iterate the folders and files, please refer to this code below:

    Dim FileSystem As Object
    Dim HostFolder As String
    
    HostFolder = "C:\"
    
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    
    Sub DoFolder(Folder)
        Dim SubFolder
        For Each SubFolder In Folder.SubFolders
            DoFolder SubFolder
        Next
        Dim File
        For Each File In Folder.Files
            ' Operate on each file
        Next
    End Sub

    Then you could check file name and open it. If it is the excel file, we could use Workbooks.Open method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ryguy72 Monday, April 27, 2015 1:15 PM
    Monday, April 27, 2015 7:47 AM
    Moderator
  • Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    

    This very simple solution works only partially, try list all files in C:\WINDOWS in an Excel sheet.

    Then compare the number of files you get with that code with the Windows Explorer, my code and a 3rd party tool, e.g. Agent Ransack.

    You will be amazed how big the differences are. ;-)

    Andreas.

    • Marked as answer by ryguy72 Monday, April 27, 2015 1:15 PM
    Monday, April 27, 2015 10:13 AM
  • Actually, I don't think my initial idea will work.  A lot of these files are PDF files.  For a Word doc, or an Excel WB, or certainly a text file, I'm sure things would be fine.  I can't really rely on these PDF files, which consist of all kinds of different things.  Maybe if I save all PDFs as text files, and work with THAT, things will be fine.  I think these PDFs are just too disparate to standardize this whole process.

    Thanks for the explanation of everything everyone!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, April 27, 2015 1:18 PM