Determine name of macro contained in each excel file in a folder

J

John

I have a folder that contains several excel files, each excel file
contains a single macro.

Is it possible from a macro in another excel file to programmatically
determine the name of the macro in each of the files in the folder?
 
J

John

Gord,

Thank you for the quick reply.

I was actually on that site earlier and saw that info but I can't get
it to work. I get the error:

User Defined Type not defined

Chip's method is clearly beyond my current level of VBA ability, I'm
probably not understanding some fine point of how VBA works.

In any case I was looking for something I could comprehend :)
 
I

isabelle

hi John,

did you added a réf. for Microsoft Visual Basic For Applications Extensibility 5.3


================================================================================================================
•First, you need to set an reference to the VBA Extensibililty library.
The library contains the definitions of the objects that make up the VBProject.
In the VBA editor, go the the Tools menu and choose References.
In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3.
If you do not set this reference, you will receive a User-defined type not defined compiler error.
================================================================================================================


--
isabelle



Le 2012-05-05 16:43, John a écrit :
 
I

isabelle

also,

if you put "Option Explicit" at the top of the module, you need to declare all variables

--
isabelle



Le 2012-05-05 16:54, isabelle a écrit :
 
I

isabelle

on the VBA window, go the the Tools menu and choose References.
In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3.
If you do not set this reference, you will receive a User-defined type not defined compiler error.

--
isabelle



Le 2012-05-05 17:01, John a écrit :
 
J

John

OK, I got rained out tonight so a few extra minutes to play with this.

I checked the reference noted below, and did get further (at first it
was a compile error I think).

But now I get an error on this line (of Chip's example):

Set VBProj = ActiveWorkbook.VBProject

which says Programmatic access to Visual Basic Project is not trusted.

If I try to step again on this line of code the error changes to:

Method 'VBProject' of object '_Workbook' failed


FWIW, I'm on WinXP and Excel 2007.
 
V

Vacuum Sealed

Not sure which Version you're using but!

In 2010.

Go to Developer | Macro Security | Macro Settings...

Under:

Developer Macro Setting, make sure "Trust access to the VBA project
object model" is checked.

Earlier Versions may be:

Tools | Macro | Macro Security.

HTH
Mick.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top