Searching for Text in Macros

[RogerCooper]

I need to be able to search for text in macros. I can do this in modules, but I can't figure out how to interact with macros. For example this code:

    For Each objComponent In Application.VBE.ActiveVBProject.VBComponents
        Debug.Print objComponent.Name
        Debug.Print objComponent.CountOfLines
    Next objComponent

This works I specify CodeModules but errors for CountOfLines when I include macros. .Name does work for macros but I can't find anyplace that explains what I can do with macros.

 

[Ranman256]

for each mac in application.CurrentProject.AllMacros

next

 

[RogerCooper]

for each mac in application.CurrentProject.AllMacros

next

Doesn't help. This is slightly simpler than the code I had, but all I can read is the names of the macros. How can I read the text of the macros.

 

[Ranman256]

i used the Access export feature. Export all macros to a file.
then open the file and search.

 

[isladogs]

Several months ago I created code to search all database objects for specific text strings and optionally to replace them.

It was easy for table fields, query SQL, form & report controls.
It was also fairly easy for code in form & report modules, standard & class modules.

Having got that far, I decided to look at macros.
It was horribly complicated and took me a long time to do successfully

I also exported each macro as text files.
I then used file scripting to re-import the text into an Access table

However the macro text includes unwanted characters that need to be filtered out before you can run your search.
If I mention tristate value, does that mean anything to you?

The attached text file shows the same macro in its raw state.
Look particularly at the last section to see what I mean about unwanted code

Once that was sorted, I could search the contents (as in the screenshot).

From there it was another significant steep to add functionality to allow find & replace in macro text.
I actually posted a question about this here back in April
https://www.access-programmers.co.uk/forums/showthread.php?t=293232&highlight=macro+find+%26amp%3B+replace

I think you will find the thread useful to read
The responses (particularly that by static) were a great help in getting a solution

I can't upload the code as its part of a very large database & it will take several hours to separate that out so it will work independently.
Sorry but I don't have the time for that.

So I'll suggest 4 alternatives in the order I think is most sensible:
a) convert all your macros to VBA procedures - RECOMMENDED
b) download the free V-Tools add-in which also includes a deep SQL search. http://www.skrol29.com/us/vtools.php
This can search macros (using a different method (undocumented wizhook VBA function) but not replace text in macros.
c) You develop your own utility - put aside a month or so for that
d) You pay me for the time it will take me to extract this to run as standalone code! I very much doubt you'll want to do that

 

[RogerCooper]

I found another solution. As I already use Total Access Analyzer from FMS, I figured out how to read the information from the database the Total Access Analyzer creates. I thank everyone for the help.