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

Discussion in 'Microsoft Excel Programming' started by John, May 5, 2012.

  1. John

    John Guest

    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?
     
    John, May 5, 2012
    #1
    1. Advertisements

  2. John

    Gord Dibben Guest

    See Chip Pearson's site for programming the VBE.

    http://www.cpearson.com/excel/VBE.aspx

    Specifically "Listing all procedures in a Module"


    Gord

    On Sat, 5 May 2012 12:57:41 -0700 (PDT), John <>
    wrote:

    >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?
     
    Gord Dibben, May 5, 2012
    #2
    1. Advertisements

  3. John

    John Guest

    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 :)

    On May 5, 2:23 pm, Gord Dibben <> wrote:
    > See Chip Pearson's site for programming the VBE.
    >
    > http://www.cpearson.com/excel/VBE.aspx
    >
    > Specifically "Listing all procedures in a Module"
    >
    > Gord
    >
    > On Sat, 5 May 2012 12:57:41 -0700 (PDT), John <>
    > wrote:
    >
    >
    >
    > >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?- Hide quoted text -

    >
    > - Show quoted text -
     
    John, May 5, 2012
    #3
  4. John

    isabelle Guest

    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 :
    > 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 :)
    >
    > On May 5, 2:23 pm, Gord Dibben<> wrote:
    >> See Chip Pearson's site for programming the VBE.
    >>
    >> http://www.cpearson.com/excel/VBE.aspx
    >>
    >> Specifically "Listing all procedures in a Module"
    >>
    >> Gord
    >>
    >> On Sat, 5 May 2012 12:57:41 -0700 (PDT), John<>
    >> wrote:
    >>
    >>
    >>
    >>> 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?- Hide quoted text -

    >>
    >> - Show quoted text -

    >
     
    isabelle, May 5, 2012
    #4
  5. John

    John Guest

    Isabelle,

    > a réf. for Microsoft Visual Basic For Applications Extensibility 5.3


    I have no idea what that is! Guess I need to do a little mroe
    research.


    On May 5, 2:54 pm, isabelle <> wrote:
    > 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 :
    >
    >
    >
    > > 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 :)

    >
    > > On May 5, 2:23 pm, Gord Dibben<>  wrote:
    > >> See Chip Pearson's site for programming the VBE.

    >
    > >>http://www.cpearson.com/excel/VBE.aspx

    >
    > >> Specifically "Listing all procedures in a Module"

    >
    > >> Gord

    >
    > >> On Sat, 5 May 2012 12:57:41 -0700 (PDT), John<>
    > >> wrote:

    >
    > >>> 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?- Hide quoted text -

    >
    > >> - Show quoted text -- Hide quoted text -

    >
    > - Show quoted text -
     
    John, May 5, 2012
    #5
  6. John

    isabelle Guest

    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 :
    > 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, May 5, 2012
    #6
  7. John

    isabelle Guest

    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 :
    > Isabelle,
    >
    >> a réf. for Microsoft Visual Basic For Applications Extensibility 5.3

    >
    > I have no idea what that is! Guess I need to do a little mroe
    > research.
    >
    >
    > On May 5, 2:54 pm, isabelle<> wrote:
    >> 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, May 5, 2012
    #7
  8. John

    John Guest

    Thank you for the tip, I'll try that. But I've run out of time until
    next week.

    On May 5, 3:06 pm, isabelle <> wrote:
    > 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 :
    >
    >
    >
    > > Isabelle,

    >
    > >> a réf. for Microsoft Visual Basic For Applications Extensibility 5.3

    >
    > > I have no idea what that is! Guess I need to do a little mroe
    > > research.

    >
    > > On May 5, 2:54 pm, isabelle<>  wrote:
    > >> 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 typenot defined compiler error.
    > >> ===========================================================================­­=====================================- Hide quoted text -

    >
    > - Show quoted text -
     
    John, May 5, 2012
    #8
  9. John

    John Guest

    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.

    On May 5, 3:06 pm, isabelle <> wrote:
    > 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 :
    >
    >
    >
    > > Isabelle,

    >
    > >> a réf. for Microsoft Visual Basic For Applications Extensibility 5.3

    >
    > > I have no idea what that is! Guess I need to do a little mroe
    > > research.

    >
    > > On May 5, 2:54 pm, isabelle<>  wrote:
    > >> 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 typenot defined compiler error.
    > >> ===========================================================================­­=====================================- Hide quoted text -

    >
    > - Show quoted text -
     
    John, May 6, 2012
    #9
  10. 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.
     
    Vacuum Sealed, May 6, 2012
    #10
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. woodman650

    Self-Contained "Database" file?

    woodman650, Jun 11, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    175
    RB Smissaert
    Jun 11, 2006
  2. Guest
    Replies:
    2
    Views:
    151
    Guest
    Sep 29, 2006
  3. Guest

    For Next Loop to Make A4 in Each Sheet = Name of Each Tab

    Guest, Jul 6, 2007, in forum: Microsoft Excel Programming
    Replies:
    8
    Views:
    224
    Dave Peterson
    Jul 7, 2007
  4. Jeff
    Replies:
    3
    Views:
    174
  5. Glenn

    Append Worksheet name to each begining of each row with values

    Glenn, Feb 4, 2010, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    214
    Ryan H
    Feb 4, 2010
Loading...

Share This Page