Macros Collection

  • Thread starter Thread starter Mike O'Brien
  • Start date Start date
M

Mike O'Brien

Hi!

I'm hoping someone can provide some direction here. I've
searched online help, the object browser, and KB to no
avail.

I'm looking to evaulate the Macros collection, its
properties, etc, but cannot find a good starting point.

Any advice would be appreciated.

Thanks.
 
The collection is called "Scripts", but there's not much info about them.

As an example, the name of your first macro is:
dbEngine(0)(0).Containers("Scripts").Documents(0).Name
 
Thanks for the starter Allen.

I used this code snippet, but am getting a Type Mismatch
for .Name:

Dim spt As Script
Set spt = DBEngine(0)(0).Containers
("Scripts").Documents(0).Name

I also tried Dimming as a variant/object/documentItem.

The online help has a section on the Scripts Collection,
but indicates that Access doesn't use this shared office
component.

I really like the idea of getting the .ScriptText property
from the script object. I think it will solve my
problem. If you could advise just a bit further? Maybe
I'm declaring the wrong variable type.

Thanks!
 
Hi Mike,

Take a look at the CurrentProject.AllMacros collection for starters. What I
usually do is assign an object to an existing macro and then take a look at
the properties in the Watches window.

Hope this helps,
- Glen
 
You appear to be trying to assign the Name property to spt, which means it
should be declared as a string. You don't need to use the Set keyword to
assign properties to variables.

Dim spt As String
spt = DBEngine(0)(0).Containers("Scripts").Documents(0).Name
 
Also check that you have a reference to the DAO library under Tools |
References (from any code window).

As Glen pointed out, you can also use the AllMacros collection if you are
working with Access 2002 or 2003:
? CurrentProject.AllMacros(0).Name
 
DOUG! How have you been? I remember you helping me at
work.

What I'm trying to do with the collection is the
following: If I have a macro where one of the actions is
TransferSpreadsheet, one of the parameters is FileName. I
want to be able to evaluate it, so I needed a starter on
viewing the properties of the macro object. I do want to
do some of the figuring out for myself, but am having
difficulty starting.

Both Allen and Glen's suggestions have been helpful, but I
still can't define the appropriate object variable which
will expose the properties I seek.

Again, I do appreciate the input.

Glen: I tried the Watches window using a generic object
for the macro, but couldn't see properties like
TransferType and TableName.

Thanks.

Mike O'Brien-Walker
 
Mike, you'll probably find that not many of us use macros like this, as they
really don't provide the functionality you need.

Especially when handling file operations, there are *so* many things that
can go wrong, that a macro cannot offer the error handling you need.

The best solution will be to use VBA code instead of a macro. Include error
handling. You can easily examine the string that holds the default file
name. And if you need to show the Windows FileOpen dialog, you can use this
code to do that too:
http://www.mvps.org/access/api/api0001.htm

Hope that helps
 
I thought I recognized the name.

I'd follow Allen's latest advice: forget trying to use macros for this, and
use VBA code. It makes for much more robust applications (since you can do
error handling, which you can't do with macros). It may take a little while
longer at first, but once you're used to it, you won't be able to understand
why you weren't using VBA from day one!
 
I agree with you. I don't actually use macros, not even
the autoexec macro. Everything in code. But, I need to
work with existing databases where macros have been
heavily relied on. I need to extract and evaluate the
paramaters of the actions in their macros. This is what I
cannot find any documentation on.

Thanks again for any help.

Mike.
 
Mike, have you tried the undocumented SaveAsText?

That may give you some listing of what's in the macros.
 
Thanks Allen!

This is pretty much what I'm looking for. I think I can
use this to accomplish what I need.

Thank you all for your help and time.

Mike.
 
Back
Top