Get Values of Macro Objects

[modest]

The person who created this database before me used a macro that had 20bizillion SendObjects to email reports.


Using the Redemption.dll, I'm going to use VBA to send the reports without the outlook security message. In doing this I'm going to have to create an email list so that I don't have to call the function 20bizillion times by hand.


I would like to make it easier to generate these tables by breaking down the already designed macros. So, how would I go about getting the Object Type, Object Name, Output Format, To, Cc, and Subject from an already made Macro.


Additionally, what is the call to get the list of macro names? For instance there's a dao.querydefs object and a dao.tabledefs that I can loop through to get a list of table names, but what is the equivalent of a dao.macrodefs?


Thank you in advance!

 

[modest]

I think I have it. What I really needed was a way to get the names and I figured I could get to the properties. I didn't know macros were called "Scripts" so here's the code I made to loop through all the macro names.

Public Function listMacroNames()

    Dim db As DAO.Database
    Dim count As Integer
    
    Set db = CurrentDb
    count = db.Containers("scripts").Documents.count
    
    For i = 0 To count - 1
        Debug.Print db.Containers("Scripts")(i).Name
    Next

End Function

Now I still need to figure out how to find the different things the macro does, like how do I get to the "SendObject" and how do I see what it's values are?

I don't think it's in db.Containers("Scripts")(i).Properties() because I've looped through them

 

[modest]

Is it not possible to get to the Actions and Action Arguments?

 

[RoyVidar]

Have you encountered the .SaveAsText (hidden) method of the application object? If not, here's a small tryout

dim obj as object
dim db as dao.database
set db=currentdb
for each obj in db.containers("Scripts").documents
application.saveastext acmacro, obj.name, "c:\somdir\" & obj.name & ".txt"
next obj

This should produce text files of all the macros, use your favourite text editor to look in them all one by one, create a little program to read them, or do a filesearch with "A word or phrase in the file..."

The method above can be used on all objects, except tables. I think someone has posted something on this in one of the sample/faq areas too.

 

[modest]

That was definitely helpful as far as seeing what the macro looks like. I just wish it would be easier to get those objects without having to write a program to search the text file.

Thought there would be a class for macros as well that I could access, especially since it lists the child objects for each object.

Nevertheless, thank you once again roy for helpin me out. -modman

 

[RoyVidar]

Couldn't resist

Private Sub modesttext()
    Dim fs                  As Object
    Dim txt                 As Object
    Dim fls                 As Object
    Dim fl                  As Object
    Dim re                  As Object
    Dim mc                  As Object
    Dim m                   As Object
    Dim strText             As String
    Dim strOut              As String
    Dim strMacro()          As String
    Dim lngCounter          As Long
    
    Const cstrPath As String = "c:\test\"
    
    Set fs = CreateObject("scripting.filesystemobject")
    If fs.FolderExists(cstrPath) Then
        Set fls = fs.GetFolder(cstrPath).Files
    Else
        MsgBox "wrong path...", vbExclamation, "cancelling..."
        Set fs = Nothing
    End If
    
    Set re = CreateObject("vbscript.regexp")
    With re
        .MultiLine = True
        .Global = True
        .IgnoreCase = True
    End With
    
    For Each fl In fls
        Set txt = fs.OpenTextFile(cstrPath & fl.Name, 1) ' For reading
        strText = txt.ReadAll
        txt.Close
        Set txt = Nothing
        re.Pattern = "Begin(.|\n)*?End\s"
        Set mc = re.Execute(strText)
        For Each m In mc
            If (InStr(m.Value, "SendObject") > 0) Then
                ' Here's the dirty version;)
                ' Debug.Print Replace(Join(Split(m.Value, "Argument ="), ","), vbCrLf, "")
                strMacro = Split(m.Value, "Argument =")
                strOut = vbNewLine & "Name of macro (as seen in db window):   " & _
                        fl.Name & vbNewLine
                If (InStr(strMacro(0), "Macroname") > 0) Then
                    strOut = strOut & "Macro Name (from column within macro): " & _
                        Split(Split(strMacro(0), "Action")(0), "=")(1) & vbNewLine
                End If
                strOut = strOut & "SendObject "
                For lngCounter = 1 To UBound(strMacro)
                    strOut = strOut & _
                        Replace(Trim$(strMacro(lngCounter)), vbNewLine, "") & ", "
                Next lngCounter
                Debug.Print Left$(strOut, Len(strOut) - 6)
            End If
        Next m
    Next fl
    
    Set fl = Nothing
    Set fls = Nothing
    Set txt = Nothing
    Set fs = Nothing
    Set m = Nothing
    Set mc = Nothing
    Set re = Nothing
End Sub

 

[modest]

Wow Roy, I wish I would have seen your post. I did it by hand ... but just testing this worked great.

For me personally, I know I'll have to use something like this again in the future, just because everyone at my work likes to use Macros and they don't seem to use email lists to email reports from access.

This means I'll have to scan through other macros and get this information again. So for me, this was plenty useful and I think others could use this as well.

Blame microsoft for not developing a macro object you can access in the VBE.. or, for making it hard to get to it if there is one.

Thanks again Roy.. you definitely did more work than I expected you to =)