Searching for Text in Macros (1 Viewer)

RogerCooper

Registered User.
Local time
Today, 07:51
Joined
Jul 30, 2014
Messages
277
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.
 

Ranman256

Well-known member
Local time
Today, 10:51
Joined
Apr 9, 2015
Messages
4,339
for each mac in application.CurrentProject.AllMacros

next
 

RogerCooper

Registered User.
Local time
Today, 07:51
Joined
Jul 30, 2014
Messages
277
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

Well-known member
Local time
Today, 10:51
Joined
Apr 9, 2015
Messages
4,339
i used the Access export feature. Export all macros to a file.
then open the file and search.
 

isladogs

MVP / VIP
Local time
Today, 14:51
Joined
Jan 14, 2017
Messages
18,186
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

  • Autokeys.txt
    17.6 KB · Views: 190
  • MacroSearch.jpg
    MacroSearch.jpg
    48.5 KB · Views: 784
Last edited:

RogerCooper

Registered User.
Local time
Today, 07:51
Joined
Jul 30, 2014
Messages
277
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

Top Bottom