PC Review


Reply
Thread Tools Rate Thread

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

 
 
John
Guest
Posts: n/a
 
      5th May 2012
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?
 
Reply With Quote
 
 
 
 
Gord Dibben
Guest
Posts: n/a
 
      5th May 2012
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 <(E-Mail Removed)>
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?

 
Reply With Quote
 
 
 
 
John
Guest
Posts: n/a
 
      5th May 2012
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 <(E-Mail Removed)> 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 <(E-Mail Removed)>
> 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 -


 
Reply With Quote
 
isabelle
Guest
Posts: n/a
 
      5th May 2012
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<(E-Mail Removed)> 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<(E-Mail Removed)>
>> 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 -

>

 
Reply With Quote
 
John
Guest
Posts: n/a
 
      5th May 2012
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 <(E-Mail Removed)> 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<(E-Mail Removed)> *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<(E-Mail Removed)>
> >> 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 -


 
Reply With Quote
 
isabelle
Guest
Posts: n/a
 
      5th May 2012
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.
> ================================================================================================================
>
>

 
Reply With Quote
 
isabelle
Guest
Posts: n/a
 
      5th May 2012
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<(E-Mail Removed)> 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.
>> ===========================================================================*=====================================

 
Reply With Quote
 
John
Guest
Posts: n/a
 
      5th May 2012
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 <(E-Mail Removed)> 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<(E-Mail Removed)> *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 -


 
Reply With Quote
 
John
Guest
Posts: n/a
 
      6th May 2012
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 <(E-Mail Removed)> 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<(E-Mail Removed)> *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 -


 
Reply With Quote
 
Vacuum Sealed
Guest
Posts: n/a
 
      6th May 2012
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to save Excel, using macro which takes the name from a cell contained in it? moqadasi@gmail.com Microsoft Excel Worksheet Functions 1 6th Sep 2006 04:43 PM
How do I Print Multiple worksheet contained under one file name? =?Utf-8?B?RHIuIENyb3diYXI=?= Microsoft Excel Misc 5 18th May 2006 04:24 PM
How to check if each value in a cell range is contained in a second cell range? ModelerGirl Microsoft Excel Discussion 3 31st Jan 2006 01:28 PM
Function to determine if any cell in a range is contained in a given cell choxio@yahoo.com Microsoft Excel Worksheet Functions 3 7th Feb 2005 05:19 PM
How to save Excel macro using name contained in cell? =?Utf-8?B?Q0c=?= Microsoft Excel Worksheet Functions 1 3rd Feb 2005 04:51 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:25 AM.