Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-24-2017, 06:33 AM   #1
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 131
Thanks: 4
Thanked 11 Times in 10 Posts
RogerCooper is on a distinguished road
Searching for Text in Macros

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 is offline   Reply With Quote
Old 10-24-2017, 06:48 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,159
Thanks: 0
Thanked 690 Times in 675 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Searching for Text in Macros

for each mac in application.CurrentProject.AllMacros

next
Ranman256 is offline   Reply With Quote
Old 10-24-2017, 07:00 AM   #3
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 131
Thanks: 4
Thanked 11 Times in 10 Posts
RogerCooper is on a distinguished road
Re: Searching for Text in Macros

Quote:
Originally Posted by Ranman256 View Post
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.

RogerCooper is offline   Reply With Quote
Old 10-24-2017, 08:10 AM   #4
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,159
Thanks: 0
Thanked 690 Times in 675 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Searching for Text in Macros

i used the Access export feature. Export all macros to a file.
then open the file and search.
Ranman256 is offline   Reply With Quote
Old 10-24-2017, 09:34 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,379
Thanks: 92
Thanked 1,815 Times in 1,690 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Searching for Text in Macros

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...amp%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
Attached Images
File Type: jpg MacroSearch.jpg (48.5 KB, 132 views)
Attached Files
File Type: txt Autokeys.txt (17.6 KB, 60 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-24-2017 at 09:52 AM. Reason: Added screenshot
isladogs is offline   Reply With Quote
Old 10-24-2017, 09:51 AM   #6
RogerCooper
Newly Registered User
 
Join Date: Jul 2014
Posts: 131
Thanks: 4
Thanked 11 Times in 10 Posts
RogerCooper is on a distinguished road
Re: Searching for Text in Macros

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.

RogerCooper is offline   Reply With Quote
Reply

Tags
macro

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching macros.....is it possible? sconly Macros 2 10-22-2008 03:46 AM
Populate a form with text using macros Ligaya Forms 6 02-10-2008 12:22 AM
Text Searching jk12 Forms 2 09-04-2007 06:17 AM
Importing a Text File Using Macros dfrankel Macros 1 07-20-2007 06:04 AM
Transfer text in macros Clare Macros 1 10-16-2002 08:26 AM




All times are GMT -8. The time now is 05:16 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World