Need search utility that scans all macro code in a folder for certain text.

  • Hi,


    I have a folder containing hundreds of xlsm files.


    Can anybody recommend a utility that scans for text in macros ( VBE/IDE ?) and not in the actual worksheets.


    I often know a keyword that is used in a particular macro, but am unaware of which xlsm file uses it.


    A macro that can actually do this, would be sensational.


    Thanking you in advance.


    Kevin

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Try...



    Hope this helps!

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Hi Domenic


    I have been watching this thread because it's something I'd find very useful.


    Firstly it works perfectly. However did you know Excel does the impossible? Well it's doing it on my computer anyway.


    When it gets to this


    Code
    1. ThisWorkbook.Worksheets.Add
    2. Range("A1").Value = "Folder"


    It writes the values to the sheet that was visible before Worksheets.Add weird.


    I just had to work around that and it's great, something I'll find useful.


    Many thanks :)

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Silly me. The above test results were when running from Sheet Module. I changed it to a Standard Module to try from there but I must have accidentally clicked back into the Sheet Module :confused:

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Thanks Domenic - this looks very promising, but I have slight problem.


    I am getting the following error message:


    Error 1004: Programmatic access to Visual Basic Project is not trusted


    I am using Excel 2010.


    Is there something else that I must do to make this work ?


    Cheers - Kevin

  • Re: Need search utility that scans all macro code in a folder for certain text.


    You need to go into your trust centre settings and enable programmatic access to VBP, which is disabled by default for security purposes.


    Personally, I would only advise enabling this setting for the duration of your macro, then disabling it again.

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Many thanks for your advice 'S O' that made the macro work fine.


    Many thanks also to Domenic for his authorship of the macro.


    Use of that macro will save me a massive amount of time - I no longer need to browse tons of xlsm files !



    Best regards - Kevin

  • Re: Need search utility that scans all macro code in a folder for certain text.


    holycow


    I'm glad that you've sorted it out, and that you've found this helpful.


    @SO


    Thanks for helping out. I really appreciate it.


    Kevin


    I've added some error handling that checks whether there's access to the VBA project...



    Cheers everyone!

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Hi again Domenic,


    Thanks for your update, which works well.


    You have done more than enough to satisfy my request,
    but can I tentatively suggest the following if it is not too difficult.


    It would put the cherry on the cake, if you could utilise column "C"
    to show the full line of code where the text is found.
    That would give a complete picture of the finds, in the event of multiple occurrences.


    I can understand if you think that you have already
    provided us with enough code to keep us happy, but I thought I would just mention it.


    Regards - Kevin

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Sure, no problem. The following macro will create a list that includes the name of the folder, the workbook name, the name of the VB component/module, and the actual line of code. If you don't want to include the name of the module and need help with making this change, post back.



    Hope this helps!

  • Re: Need search utility that scans all macro code in a folder for certain text.


    That certainly does help a lot Domenic - it works like a dream.


    You have been incredibly helpful and I can't thank you enough.


    So - Thanks a million.


    Best regards - Kevin

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Quote from PeteRooneyHome;732934

    I WISH I'd had this three weeks ago. What a brilliant piece of coding! Thank you VERY much!


    Pete


    You're very welcome, and thank you for your kind words.


    Cheers!

  • Re: Need search utility that scans all macro code in a folder for certain text.


    Quote from Ingo_Ingo;732973

    Domenic,


    Excellent!!!


    Thanks so much.


    I'm glad you found it helpful. Thanks for your feedback.


    Cheers!