Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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 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), 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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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 *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), 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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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. ================================================== ================================================== ============ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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, 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. ================================================== =========================*======================== ============= |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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, *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. ================================================== =========================**======================= ==============- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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, *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. ================================================== =========================**======================= ==============- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine name of macro contained in each excel file in a folder
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying sheet1 from different workbooks contained in a folder to myone and only destination workbook. | Excel Programming | |||
add the macros contained inside a template to an Excel file. | Excel Discussion (Misc queries) | |||
Problems with opening a VBA contained excel file? | Setting up and Configuration of Excel | |||
Need a macro to include file names in a folder to excel sheet | Excel Discussion (Misc queries) | |||
How to determine if a Folder/Directory Exists in Excel VBA | Excel Programming |