Searching for Text in Macros (1 Viewer)

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
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:

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.
 

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
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

Registered User
Joined
Apr 9, 2015
Messages
3,423
i used the Access export feature. Export all macros to a file.
then open the file and search.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,315
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 :D
 

Attachments

Last edited:

RogerCooper

Registered User
Joined
Jul 30, 2014
Messages
166
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom