Extracting info from Macros via VBA

[Stuart]

Hi all,

Can anybody recommend a nice way to get to the contents of
a macro via VBA? I need to find where functions or objects
are being used by macro commands (e.g. RunCode). I've
considered using "DoMenuItem" & "Convert Macros To Visual
Basic" (from the "Tools" menu), and extracting the info I
want that way but that seems a really
ugly workaround.

I'd do anything for a nice little Macro object...

Many thanks,

Stuart

 

[Steve Schapel]

Stuart,

You can use the Scripts container in VBA to access the Macros, but
AFAIK this only exposes things like Macro Name, Date Created, etc.
There is the report available via the Tools|Analyze|Documenter menu,
which might help. Other than that, there are some third-party tools
available that can do what you ask, for example Total Access Analyzer
from http://www.fmsinc.com

- Steve Schapel, Microsoft Access MVP

 

[Jim/Chris]

I am not sure if this is what you want but you can convert
your macros to VB. They are not the equivelant VB commands
but macro commands run in VB. To access highlight a macro
and choose Tools/Macro/Convert Macros to Visual Basic. The
converted macro will be under the modules section.

Jim

 

[Stuart]

I'd tried converted the macros to VBA, but fully
automating it proved to be quite a problem. Because you
need to use the RunCommand (or DoMenuItem if you're
feeling a bit retro) you cannot avoid the box that pops up
asking if you want to add error handling etc. to the
module.

Private Sub ExtractMacro(ByVal adoc As DAO.Document)

Dim mdl As Module '[Used to extract information from
the macro via VBA]
Dim strConvertedMacroName As String '[The name of the
macro when converted to VBA]
Dim strModuleText As String '[The body of the
converted module]

On Error GoTo Error_Handler

'[Select the macro in the database window so we can
play around with it]
DoCmd.SelectObject acMacro, adoc.Name, True
'[Convert the macro to Visual Basic so we can get to
its details]
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
'[Calculate the name of the converted macro module]
strConvertedMacroName = "Converted Macro- " & adoc.Name
'[Open the converted macro module]
DoCmd.OpenModule strConvertedMacroName
'[Save the module - so we can delete it when we're
finished with it!]
DoCmd.Save acModule, strConvertedMacroName
'[Set a reference to the converted macro module]
Set mdl = Modules(strConvertedMacroName)
'[Get the text of the module]
strModuleText = mdl.Lines(1, mdl.CountOfLines)
'[Delete the converted macro module now we're done
with it]
DoCmd.DeleteObject acModule, strConvertedMacroName

 

[Stuart]

Sorry, I hit return before finishing my last message!

I meant to add that my problem with the code I included in
the last message lies in the "Docmd.RunCommand" line of
code. I've tried "SendKeys" to get rid of the box that
pops up (a very ugly approach I'm not too proud of!)
and "SetWarnings" to try to hide the box from the users
but all to no avail. Any ideas how I can avoid this
infernal box?

Cheers,

Stuart

-----Original Message-----
I'd tried converted the macros to VBA, but fully
automating it proved to be quite a problem. Because you
need to use the RunCommand (or DoMenuItem if you're
feeling a bit retro) you cannot avoid the box that pops up
asking if you want to add error handling etc. to the
module.

Private Sub ExtractMacro(ByVal adoc As DAO.Document)

Dim mdl As Module '[Used to extract information from
the macro via VBA]
Dim strConvertedMacroName As String '[The name of the
macro when converted to VBA]
Dim strModuleText As String '[The body of the
converted module]

On Error GoTo Error_Handler

'[Select the macro in the database window so we can
play around with it]
DoCmd.SelectObject acMacro, adoc.Name, True
'[Convert the macro to Visual Basic so we can get to
its details]
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
'[Calculate the name of the converted macro module]
strConvertedMacroName = "Converted Macro- " & adoc.Name
'[Open the converted macro module]
DoCmd.OpenModule strConvertedMacroName
'[Save the module - so we can delete it when we're
finished with it!]
DoCmd.Save acModule, strConvertedMacroName
'[Set a reference to the converted macro module]
Set mdl = Modules(strConvertedMacroName)
'[Get the text of the module]
strModuleText = mdl.Lines(1, mdl.CountOfLines)
'[Delete the converted macro module now we're done
with it]
DoCmd.DeleteObject acModule, strConvertedMacroName

-----Original Message-----
I am not sure if this is what you want but you can convert
your macros to VB. They are not the equivelant VB commands
but macro commands run in VB. To access highlight a macro
and choose Tools/Macro/Convert Macros to Visual Basic. The
converted macro will be under the modules section.

Jim

.

.