VBA Code to Loop Through Macro Modules (1 Viewer)

grifter · May 3, 2012

Hi

Any ideas how to access macro modules using VBA, the macros run queries and other macros and I have quite a lot. Instead of opening each one and checking for different queries and what is in each I want to do it in code. I have done similar with my queries and tables using:

For Each tdf in db.tablesdefs

For each qdf in querydefs

But what is it you use for macros?

Reason i am doing this is because I am copying a DB and using it for other sub sites where all I need to do is change some built in names in queries and tables, and so need to do the same with query names that are in macros.

Thanks

G

JANR · May 3, 2012

Not that I am aware of, the only way I ca think of is to export the macros to a text file and edit the textfile and import the macros back.

Application.SaveAsText

and to load the modified macro back using

Application.LoadFromText

Here is a code to export all macros to current folder where your db is:

Code:

Function ExportAllMacros()
Dim obj As AccessObject
Dim dbs As Object
Dim Destination As String

Destination = CurrentProject.Path & "\"
Set dbs = Application.CurrentProject

    For Each obj In dbs.AllMacros
        Application.SaveAsText acMacro, obj.Name, Destination & obj.Name & ".txt"
    Next
End Function

I fear that by the time you have come up with a code to manipulate the textfile and imported it back, it would be faster to open the macros and edit them manually.

How ever if you have embedded macros in forms the they wil NOT be exported as they are the property of the forms they belong to.

Not much help

JR

Pat Hartman · May 4, 2012

If you are not wedded to the macros, use the button on the ribbon to convert them to VBA.

VBA Code to Loop Through Macro Modules (1 Viewer)

grifter

Registered User.

JANR

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread