Find/Locate Files/Workbooks Containing Macros

  • Re: Finding Files Containing Macros


    Hi Stephen,


    You could start with something simple like this:

    This only looks for standard modules. You may wish to look into counting procedure lines in the objects like ThisWorkbook too.


    EDIT : Sorry X, didn't notice your post (and can't check files from work anyway!)

  • Re: Finding Files Containing Macros


    Thank you gentlemen. I'm having problems with both so further help would be very welcome.


    xlite -
    Your script tells me there are no matching files when I know at least one file in the folder has a macro (without a password).


    Richie -
    I'm getting a compile error

    Quote

    User-defined type not defined

    on the line

    Code
    Dim vbc As VBComponent


    Any thoughts?

  • Re: Finding Files Containing Macros


    Quote from StephenR

    xlite -
    Your script tells me there are no matching files when I know at least one file in the folder has a macro (without a password).



    hi Stephen,


    i forgot to mention that you need to "close" the path with "\"
    in sheet 2 cell A1:
    C:\Documents and Settings\HP_Administrator\My Documents\My Excel\
    notice the "\" after "My Excel" folder


    hth

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: Finding Files Containing Macros


    Thanks again guys. You'll start to wish you'd never seen this thread but I'm still having problems with both.


    xlite - it now lists every file in the folder, most of which do not contain any modules.


    Richie - I'm getting a run-time error

    Quote

    Method 'VBProject of Object_Workbook failed

    on this line

    Code
    For Each vbc In wbk.VBProject.VBComponents


    I presume I added the correct reference - VB for Applications Extensibility 5.3?


    Thanks again for your efforts so far.


    EDIT - xlite, I may have misunderstood yours. Having looked again does it list every filename and then alongside any module names? If so, it's still not quite right as it doesn't do the latter.

  • Re: Finding Files Containing Macros


    StephenR,


    Please check to see if you are set up to access the VBProject area. In Excel, click on Tools, then Macro, then Security, and then the Trusted Publishers (or Sources) tab and check the "Trust access to Visual Basic Project" box. That should let you get the second list using xlite's code.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!