Link to home
Start Free TrialLog in
Avatar of shannonds
shannonds

asked on

Access 2007 - VBA - List Macro actions to csv file

Hello all,
I'm looking for a way, using Access 2007 vba, to output a list of all Marco actions to a CSV file.

For example:

The field headings would be:
MacroName, Action, Comment, Argument

Sample data might look like this:
AutoUpdate, RunMacro, Create Reports, mcrRunReports
AutoUpdate, SetWarnings, Turn off warnings, 0
AutoUpdate, OpenQuery, Run update query to change run date, qryUpdateRunDate
AutoUpdate, SetWarnings, Turn on warnings, -1
mcrRunReports, SetWarnings, Turn off warnings, 0
mcrRunReports, RunCode, Run code to print user reports, DistributeReports ("qryReportList")

etc...

Any help would greatly be appreciated.
Thanks...
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

test this command and perhaps you can parse the resulting text file

application.saveastext acmacro, "Macro1",currentproject.path & "\mcMacro1.txt"
Avatar of shannonds
shannonds

ASKER

I'm already able to output the Macros as Text files, but that's not really what I want to do.  

I want all the actions of each macro in one file, laid out just the way I stated.

MacroName, Action, Comment, Argument
sorry, but AFAIK, there is no direct way of exporting Macro as CSV/Text file.

you have to use vba codes to parse the Text file output from the SaveAs command.
This just... just... might be possible using VBA Extensibility, but:

1. Only procedures that are functions have an output to capture
2. The output isn't necessarily a scalar

and, most importantly, the work would be greater that simply modifying the code to produce an output
The Database documenter will do something "similar" to what you are asking for:
User generated imageSaving this output as a Word RTF document will result in more of the output style you want.
More so than outputting to a text file
If I knew where the Database documenter was getting it's info, I might be able to parse out the info I need.  Ideally I'd like to fill a treeview control with the info.  This way it would be a lot easier to trouble-shoot a Macro which has several nested Macros and queries in it.

Something that ends up looking like this:
User generated image
have you tried converting your macros to VBA?
reading VBA codes is easier than reading macros
Oops.
In case there are any others who, like me, haven't used one for years and had completely forgotten, in Access, a Macro is not a VBA procedure as in other Office products, but is a list of Access commands.
did somebody mentioned "Macro is a VBA procedure " ?
I did try converting the macro's to VBA, but it's not giving me what I need.  If there was some system table that contained the macro information that I could pull from, that would be ideal, but I don't know of one.  I'm curious where the Database documenter is getting it's info.  Maybe I could parse that info.
Some things are internal to Access
(Like the info behind the system tables)
...so I don't think what you are asking for here can be done *easily* (if at all)

Why not just add comments between each macro "step", ...then your macro will be more "understandable" when you view it in design view:
User generated imageTo add a comment to a macro, create a new action, and type in "//" before your comment text.
ex.:
    //This will be a macro comment

At the other end of the spectrum, I would ask if this is a web database, where macros are required.
If not, then you may be better to move to VBA coding where comments are part of the programming environment.
Then you can just print your code,...and no machinations are needed to make it "understandable"
ex.:

'Update all customer info
DoCmd.OpenQuery "qryUpdateCustInfo"
'Create the table
DoCmd.OpenQuery "qryCreateTable"
'Append the data
DoCmd.OpenQuery "qryAppend"

'Build the table (this simply runs another code procedure or function
Call BuildTable

Open in new window


..add to this all of the other benefits of coding over macros:
Robust error handling
Libraries/references
Complex constructs (SELECT CASE, recordsets, Property SET)
Debugging tools
The ability to make your code "portable"
...etc

The Access Macro language is only used in Access, (not in Word, or Excel, ...etc)
So learning VBA will make it easy for you to migrate your skills up to other, more robust programming platforms.(vb.net, c# ...et al)

JeffCoachman
ASKER CERTIFIED SOLUTION
Avatar of shannonds
shannonds

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've been using Access since the very beginning I'm well verse on what the macros do and they are fairly well commented.
Oh, ...OK.
;-)

I understood what you were after, I just was not 100% sure it could not be done.
 I was hoping that Rey might have known a way...

Sorry I could not help more.

JeffCoachman
It appears that this can't be done, so we might as well close the question.  Thanks anyway