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...
shannondsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this command and perhaps you can parse the resulting text file

application.saveastext acmacro, "Macro1",currentproject.path & "\mcMacro1.txt"
0
shannondsAuthor Commented:
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
0
Rey Obrero (Capricorn1)Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GrahamSkanRetiredCommented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
The Database documenter will do something "similar" to what you are asking for:
screenSaving 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
0
shannondsAuthor Commented:
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:
Snapshot of sample macro tree
0
Rey Obrero (Capricorn1)Commented:
have you tried converting your macros to VBA?
reading VBA codes is easier than reading macros
0
GrahamSkanRetiredCommented:
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.
0
Rey Obrero (Capricorn1)Commented:
did somebody mentioned "Macro is a VBA procedure " ?
0
shannondsAuthor Commented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
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:
macro commentsTo 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
0
shannondsAuthor Commented:
I appreciate your efforts, but whereas I've been using Access since the very beginning I'm well verse on what the macros do and they are fairly well commented.  My reason for asking the question in the first place was to add functionality to an MS Access tool I wrote (aka Object Maintenance Form).  One of the current features of this tool creates what I refer to as a Query Research Tree.  The user selects a query and then by clicking the Query Research button, the user is taken to a treeview of all the objects used to create the selected query.  By double-clicking any of the objects in the tree, depending on the object type the user is either taken to the design mode of the selected query or the display mode of the selected table.  This comes in very handy, when troubleshooting an issue with an action query that has many nested objects.  (See attachment)
What I was hoping to do, was something similar for Macros...  Unfortunately, it doesn't appear to be possible.
QueryResearchTree.JPG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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
0
shannondsAuthor Commented:
It appears that this can't be done, so we might as well close the question.  Thanks anyway
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.