[X]My Assistant

[hunuyolau]

Hi there,


I am trying to list out the queries name and RunCode name that are used in a Macro. I would like to list them out in a table of the Access. Is there any VBA/built-in function in Access directly serve the propose?


I know that Access has a documenter function and can export the macro detail into an .xls or .txt. I would like to see if there is method that can directly list out in Macro detail without massage it in Excel.


Anyone has idea on how to do that? Thanks


hunuyo

[theDBguy]

Hi. Welcome to UA!

You may have to create/build your own or try out third-party apps.

PS. I could be wrong but I think it's easier to do something like this with a Module than a Macro.

[isladogs]

You can get a list of all database object names from the MSysObjects system table (normally hidden).
The object type is identified using the Type field e.g. Type =5 for queries and -32766 for macros.

AFAIK there is no simple way of exporting macros to a text or Excel file.
I did write some complex code that allows this as part of a deep search routine but its really not worth the effort for most purposes.

Perhaps you are confusing macros with module code which is just text and easily exported.

[hunuyolau]

Hi isladogs and theDBguy,

Let me try to further explain what i am trying to do. Here is an example. From the attached file, It is an Macro report that exported through Database docementer in Access. What I particularly want to extract is the data in cell G17 to I19.

I would be perfect if I can print it in an Access table directly and hopefully no need to deal with Excel.

Attached File(s)

 doc_rptObjects.zip ( 1.86K )Number of downloads: 5

 

[isladogs]

As you've found already exporting the contents of a macro via the database documenter includes all sorts of 'stuff' that you don't actually want.
There is no easy way of getting what you want for macros from Access.
If you instead used VBA code, it would be much more straightforward.

BUT if you must use macros, then I can offer a couple of alternatives.

1. Download the free V-Tools add-in which includes a deep search facility
Although excellent, it is fairly limited for macros. Here is an example of its output for macros:

 V_Tools_DeepSearch.PNG ( 19.49K )Number of downloads: 3


2. Investigate whether commercial add-ins such as MZ Tools include this feature (at a cost)

3. Consider whether my deep search feature could provide what you want. Here are an example form and report of its output for an autokeys macro

 SQLSearchForm.PNG ( 95.37K )Number of downloads: 4

 SQLSearchReport.PNG ( 29.53K )Number of downloads: 1

This is the macro in question:
 Autokeys_macro.PNG ( 42.28K )Number of downloads: 2


If this looks any use, please PM or email me to discuss further
Its an integral part of a commercial app so I would have to charge for the code.
It would also take time to extract the code so it could be used with another app

Personally I would strongly recommend you give up using macros and move over to VBA code instead