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: 126
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,071
Thanks: 0
Thanked 674 Times in 659 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: 126
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,071
Thanks: 0
Thanked 674 Times in 659 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: 6,530
Thanks: 88
Thanked 1,608 Times in 1,498 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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, 128 views)
Attached Files
File Type: txt Autokeys.txt (17.6 KB, 59 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web 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: 126
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 12:35 AM.


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

Sponsored Links

How to advertise

Media Kit


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