Create a macro using VBA

[tbellomo]

First off, I know -- "Why?"

But before we get into "why," just let me know, is it possible?

Can I manipulate/create macros from VBA? Possibly through:

DBEngine(0)(0).Containers(5).Documents(x)

Or is the a table that stores the macro definitions, similar to MSysQueries?

Any help would be appreciated?

As for "why?" There's a high turnover at my company -- new people get a stack of "run documentation" for numerous reports. I didn't like the way this worked, so I created a process for scheduling these recurring reports. But the other Business Analyst (I'm more of a Programmer Analyst) couldn't find their way around my code, and I had a hard time piecing together the undocumented code that my predecessors left me. I decided that it should be easy enough for a normal user to understand which queries are being run (in what order), what it's exported to, and who it's distributed to. For this, the macro has the easiest layout. So what I'm trying to due is build a module that will facilitate the creation of these macros (so I can eventually have the non-programmers be able to schedule their legacy reports). Anyway, trust me -- there is a good "why".

 

[RoyVidar]

It should probably be doable, though I've never tried.

I think the macro properties available through the method you describe, are a bit limited, and I really don't know if it would work using that.

Though they are undocumented, I think I'd tried taking a look at the .saveastext/.loadfromtext methods of the application object - but since they are not documented/supported, use with care, and add some disclaimers to the client/users;-)

Here's a faq on how one might use those methods to retrieve information from macros faq705-5796, and here's a thread with some more information about these methods thread705-834817 (and follow the link within).

Good Luck!

Roy-Vidar