Searching Files in Subfolders for VBA code string

We have an unwritten rule where I work which goes: No major changes on a Friday!

Being a Friday (NZDT), it was a good day for housekeeping the systems I look after.

There were a few database Views which I’ve been wanting to tidy up. These particular Views kind of got “out of control” so I had to hunt down all the places where they were used.
Unfortunately, the locations were never registered.

Fortunately, it’s use was limited to ADO calls from within Excel code modules. All of the files were in the same Folder – buried under several layers of subfolders.

Here is a bit of code which opens each XL file in a folder (and subfolders), checks each code module for a string and when found reports: FileName, ModuleName, LineNumber

8 thoughts on “Searching Files in Subfolders for VBA code string

  1. Joseph,

    I could have gone: Set rng = rng(2)
    but I’m not a big fan of that method because I believe it reduces readability.

    That said, I still use it sometimes…
    Set rng = Selection(1)

    Cheers,
    Rob

  2. When I run the code, I get an error saying that “Programmatic access to Visual Basic Project is not trusted”.

    Is there a security setting that I need to change?

  3. Matt

    You need to do the following. On the menu go to ToolsMacroSecurity, select the “Trusted Publishers” tab and check the option “Trust access to Visual Basic Project” and the code should run. I’m using Excel 2003 but as far as i’m aware it should be the same process in 2000 and 2002.

    John

  4. Hi,

    Nice blog, thanks. I’m trying to find a file that has all the Excel enumerations set out – something like a header or some other “include” thing.

    So far unsuccessful – any hints?

    Cheers.

    Grant

  5. Hi,

    I tried this and get the following errors (after setting up Microsoft Visual Basic for Applications Extensibility) and changing the directory as follows:

    Sub XLS_Files_Search_Code_For_String()
    Const cFolder = “J:00\PhD\Data and Analysis” ‘there should be slashes here
    Const cFile = “ * .xl * ”
    Const cTarget = “MyView_VW”

    The cFolder line gives me a “compile error: Expected: line number or label or statement or end of statement”

    If I comment this line out just to see what happens, the cFile line gives me “compile error: Invalid or unqualified reference”

    Any ideas?

    Thanks very much

  6. I used to use this a lot and it was awesome. After many years, I had a need for it again… but these days, it doesn’t work. Application.FileSearch is a depracated method in Excel 2013.

    Did this ever get updated to suit modern times?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.