none
Excel & Word Macros: Where stored and how to install?

    Question

  • 1) When I create an Excel or Word module and enter code into it, I understand that that creates a macro. But where is that macro stored? I mean, is it available to all workbooks and documents from there on in? or must it be recreated in each? For MS Word is it placed in the Normal.dot file and then used in all documents from there?

    2) Is it possible to create an installation program ( using an MS installer) to "install" the macros for Excel, Word?

    Saturday, February 26, 2011 10:51 PM

All replies

  • In Word, Macros are stored in modules that  can be located in documents or templates and templates can be stored in either the templates folder in which case they are used by selecting New from the File menu and then selecting the template that you want to use as the basis for the document that you are creating, or they can be stored in the Word Startup folder in which case, when Word is started, the template is loaded as an Add-in.

    When a template is stored in the templates folder, the macros that the template contains are available for use with all documents created from that template (as long as the documents are opened on a machine on which that template is installed).  With templates stored in the Word Startup folder, the macros in the template are available for use with all documents on the machine.

    Templates stored in a document are available only for use with that particular document and while the macros will travel with the document, you cannot guarantee that the recipient of the document will be able to use the macros as, if their macro security level is set to High, the macros will be disabled without the user having to do anything.  If the Security level is set to Medium, the user will be given the option of allowing or dis-allowing the macros.  These security levels are the ones in effect on the recipients machine and cannot be changed by code in the document.

    In versions of Word before 2007, there was no distinction between documents (*.doc) and templates (*.dot) that contained macros and those that did not. From Word 2007, documents and templates that do not contain macros have a docx and dotx file extension respectively and those that do contain macros have docm and dotm extensions respectively.

    When you create a macro in Word, if you do not specifically select where the macro is to be created, it will be created in the Normal.dot or Normal.dotm template (depending upon your version of Word).

    See the article "What do Templates and Add-ins store?” at:

    http://www.word.mvps.org/FAQs/Customization/WhatTemplatesStore.htm

    and "What do I do with macros sent to me by other newsgroup readers to help me out?” at:

    http://www.word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

    For the way in which to distribute macros, see the article "Distributing macros to other users” at:

    http://www.word.mvps.org/FAQs/MacrosVBA/DistributeMacros.htm
     -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "LouOttawa" wrote in message news:f0122324-0837-495a-b263-fe93564594b8@communitybridge.codeplex.com...

    1) When I create an Excel or Word module and enter code into it, I understand that that creates a macro. But where is that macro stored? I mean, is it available to all workbooks and documents from there on in? or must it be recreated in each? For MS Word is it placed in the Normal.dot file and then used in all documents from there?

    2) Is it possible to create an installation program ( using an MS installer) to "install" the macros for Excel, Word?


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Sunday, February 27, 2011 3:24 AM
  • In Word, Macros are stored in modules that  can be located in documents or templates and templates can be stored in either the templates folder in which case they are used by selecting New from the File menu and then selecting the template that you want to use as the basis for the document that you are creating, or they can be stored in the Word Startup folder in which case, when Word is started, the template is loaded as an Add-in.

    When a template is stored in the templates folder, the macros that the template contains are available for use with all documents created from that template (as long as the documents are opened on a machine on which that template is installed).  With templates stored in the Word Startup folder, the macros in the template are available for use with all documents on the machine.

    Templates stored in a document are available only for use with that particular document and while the macros will travel with the document, you cannot guarantee that the recipient of the document will be able to use the macros as, if their macro security level is set to High, the macros will be disabled without the user having to do anything.  If the Security level is set to Medium, the user will be given the option of allowing or dis-allowing the macros.  These security levels are the ones in effect on the recipients machine and cannot be changed by code in the document.

    In versions of Word before 2007, there was no distinction between documents (*.doc) and templates (*.dot) that contained macros and those that did not. From Word 2007, documents and templates that do not contain macros have a docx and dotx file extension respectively and those that do contain macros have docm and dotm extensions respectively.

    When you create a macro in Word, if you do not specifically select where the macro is to be created, it will be created in the Normal.dot or Normal.dotm template (depending upon your version of Word).

    See the article "What do Templates and Add-ins store?” at:

    http://www.word.mvps.org/FAQs/Customization/WhatTemplatesStore.htm

    and "What do I do with macros sent to me by other newsgroup readers to help me out?” at:

    http://www.word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

    For the way in which to distribute macros, see the article "Distributing macros to other users” at:

    http://www.word.mvps.org/FAQs/MacrosVBA/DistributeMacros.htm
     -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "LouOttawa" wrote in message news:f0122324-0837-495a-b263-fe93564594b8@communitybridge.codeplex.com...

    1) When I create an Excel or Word module and enter code into it, I understand that that creates a macro. But where is that macro stored? I mean, is it available to all workbooks and documents from there on in? or must it be recreated in each? For MS Word is it placed in the Normal.dot file and then used in all documents from there?

    2) Is it possible to create an installation program ( using an MS installer) to "install" the macros for Excel, Word?


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org


    You have answered the first part for MS Word, but what about Excel? I don't know what other Office software has multi-document operations.

    As for the installation part, the information does not answer the question. I don't want to distribute the macros in the way the article speaks of. I want to "install" the macros into Word and Excel at the time that adjunct software is installed. For example, a text-to-speech converter may come with Word macros that call upon the converter.

    The 2007 conventions (.docm and .dotm) complicate matters. But that's for another post.

    Sunday, February 27, 2011 10:00 AM
  • In Word, Macros are stored in modules that  can be located in documents or templates and templates can be stored in either the templates folder in which case they are used by selecting New from the File menu and then selecting the template that you want to use as the basis for the document that you are creating, or they can be stored in the Word Startup folder in which case, when Word is started, the template is loaded as an Add-in.

    When a template is stored in the templates folder, the macros that the template contains are available for use with all documents created from that template (as long as the documents are opened on a machine on which that template is installed).  With templates stored in the Word Startup folder, the macros in the template are available for use with all documents on the machine.

    Templates stored in a document are available only for use with that particular document and while the macros will travel with the document, you cannot guarantee that the recipient of the document will be able to use the macros as, if their macro security level is set to High, the macros will be disabled without the user having to do anything.  If the Security level is set to Medium, the user will be given the option of allowing or dis-allowing the macros.  These security levels are the ones in effect on the recipients machine and cannot be changed by code in the document.

    From the above, I have the impression that:

    A user may avoid having the macro in his document if he doesn't use the template that contains it. Is that correct? Is that true regardless of where the template is put (Startup folder or the default template folder)?

    Can a user define a macro and place it into a template such that the macro is NOT stored in Normal.dot?

    For my purposes, the macro must be present no matter what templates or add-ins are used.

    Sunday, February 27, 2011 11:41 AM
  • When a document is created from a template that contains macros, the document itself will NOT contain the macros.  However, when the document is created, or opened on a machine on which the template is located, a reference to the template is created, so the macros that are IN THE TEMPLATE will be available for use with that document.

    A user can create a macro in a document, or a template other than Normal.dot.  It is just a matter of selecting the location where the user wants to create the macro, either in the VBE or in the Macros dialog via the "Macros in:" dropdown.

    I am not sure what you mean by the "macro must be present"  If you mean present no matter where the document is opened (that is on a machine that is different from the one on which it is created) while the macro may be "present" in the document because the macro was created in the document. as mentioned in my previous post, that does not mean that the macro will be available due to the security settings on the computer on which it is opened.

    Please describe exactly what it is that you want to achieve.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "LouOttawa" wrote in message news:2281ad55-e7b4-4416-966f-050aff5fec61@communitybridge.codeplex.com...

    In Word, Macros are stored in modules that  can be located in documents or templates and templates can be stored in either the templates folder in which case they are used by selecting New from the File menu and then selecting the template that you want to use as the basis for the document that you are creating, or they can be stored in the Word Startup folder in which case, when Word is started, the template is loaded as an Add-in.

    When a template is stored in the templates folder, the macros that the template contains are available for use with all documents created from that template (as long as the documents are opened on a machine on which that template is installed).  With templates stored in the Word Startup folder, the macros in the template are available for use with all documents on the machine.

    Templates stored in a document are available only for use with that particular document and while the macros will travel with the document, you cannot guarantee that the recipient of the document will be able to use the macros as, if their macro security level is set to High, the macros will be disabled without the user having to do anything.  If the Security level is set to Medium, the user will be given the option of allowing or dis-allowing the macros.  These security levels are the ones in effect on the recipients machine and cannot be changed by code in the document.

    From the above, I have the impression that:

    A user may avoid having the macro in his document if he doesn't use the template that contains it. Is that correct? Is that true regardless of where the template is put (Startup folder or the default template folder)?

    Can a user define a macro and place it into a template such that the macro is NOT stored in Normal.dot?

    For my purposes, the macro must be present no matter what templates or add-ins are used.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Sunday, February 27, 2011 8:30 PM
  • I am not so confident about the situation with Excel so would suggest that you post another question asking about the situation for Excel alone.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "LouOttawa" wrote in message news:b46e97aa-e4f1-46bb-9746-b3046153e11c@communitybridge.codeplex.com...

    In Word, Macros are stored in modules that  can be located in documents or templates and templates can be stored in either the templates folder in which case they are used by selecting New from the File menu and then selecting the template that you want to use as the basis for the document that you are creating, or they can be stored in the Word Startup folder in which case, when Word is started, the template is loaded as an Add-in.

    When a template is stored in the templates folder, the macros that the template contains are available for use with all documents created from that template (as long as the documents are opened on a machine on which that template is installed).  With templates stored in the Word Startup folder, the macros in the template are available for use with all documents on the machine.

    Templates stored in a document are available only for use with that particular document and while the macros will travel with the document, you cannot guarantee that the recipient of the document will be able to use the macros as, if their macro security level is set to High, the macros will be disabled without the user having to do anything.  If the Security level is set to Medium, the user will be given the option of allowing or dis-allowing the macros.  These security levels are the ones in effect on the recipients machine and cannot be changed by code in the document.

    In versions of Word before 2007, there was no distinction between documents (*.doc) and templates (*.dot) that contained macros and those that did not. From Word 2007, documents and templates that do not contain macros have a docx and dotx file extension respectively and those that do contain macros have docm and dotm extensions respectively.

    When you create a macro in Word, if you do not specifically select where the macro is to be created, it will be created in the Normal.dot or Normal.dotm template (depending upon your version of Word).

    See the article "What do Templates and Add-ins store?” at:

    http://www.word.mvps.org/FAQs/Customization/WhatTemplatesStore.htm

    and "What do I do with macros sent to me by other newsgroup readers to help me out?” at:

    http://www.word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

    For the way in which to distribute macros, see the article "Distributing macros to other users” at:

    http://www.word.mvps.org/FAQs/MacrosVBA/DistributeMacros.htm
     -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "LouOttawa" wrote in message news:f0122324-0837-495a-b263-fe93564594b8@communitybridge.codeplex.com...

    1) When I create an Excel or Word module and enter code into it, I understand that that creates a macro. But where is that macro stored? I mean, is it available to all workbooks and documents from there on in? or must it be recreated in each? For MS Word is it placed in the Normal.dot file and then used in all documents from there?

    2) Is it possible to create an installation program ( using an MS installer) to "install" the macros for Excel, Word?

    -- Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org

    You have answered the first part for MS Word, but what about Excel? I don't know what other Office software has multi-document operations.

    As for the installation part, the information does not answer the question. I don't want to distribute the macros in the way the article speaks of. I want to "install" the macros into Word and Excel at the time that adjunct software is installed. For example, a text-to-speech converter may come with Word macros that call upon the converter.

    The 2007 conventions (.docm and .dotm) complicate matters. But that's for another post.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Sunday, February 27, 2011 8:31 PM
  • but what about Excel? I don't know what other Office software has
    multi-document operations.

    Macros are stored in the Workbook. Press F11 to open the VBE (Visual Basic
    Editor), select your project in the top left pane, right click and Insert a
    (normal) Module. Alternatively just start recording a macro (not the
    location option)

    If you want your macros to load automatically  and be always available, for
    simple macros probably best to put them in your Personal. If you don't
    already have one, record a dummy macro but ensure the location (stor macro)
    is in "Personal macro workbook". Your Personal will be created and in the
    correct file location (the \XLSTART folder). Delete the dummy macro and
    write your own. When you close Excel be sure to accept the save Personal
    prompt.

    For more extensive projects probably better to store your code in an
    Installed Addin. Search those terms and come back if you still need further
    clarification.

    Peter Thornton

    Monday, February 28, 2011 9:19 AM
  • Well, your answers are meandering a bit, so maybe I can clarify :

    I am using Windows XP and running MS Office 2003. And I assume that the security is set to allow macros to run regardless of where they are stored, and regardless of user.

    For Word:
    First, "macro" may be a misnomer - I entered VBA code into Word as functions that trigger on events. (I used a class module and a normal module.) They nevertheless appear as macros. Once saved these functions are now available in every document - new or otherwise. I assume that the functions went into Normal.dot and that Normal.dot is opened with any other template that I use.

    Those functions will call other Apps (such as text-to-speech conversion) that I am developing. I therefore need to have those functions packaged in some way and installed with the Apps to get the same effect that I got when I manually entered the code.

    This is not an in-house thing; there must be no manual work to place files or enter code. The Apps get installed and used on one machine and don't operate over a network. All users of the machine must have these functions available for every document and template thereafter.

    For Excel:
    I also entered the same code into Excel and saved the file. I only get those functions back when I open that file. New files or past files do not contain the code. I need those functions to be installed with the Apps and then available in each new or re-opened workbook.

    So the questions are: How do I package the functions for installation and how do I install them to get the results that I want.

    And when I say install, I mean I will be using an installer to install the Apps.

    I know about the XLSTART folder; I have 2 of them, but which one is for all users?

    Lou.

    Monday, February 28, 2011 5:23 PM
  • Well, your answers are meandering a bit, so maybe I can clarify :

    I'm only speaking for Excel but strange you consider Doug's and my answers as meandering.

    I also entered the same code into Excel and saved the file. I only get those functions back when I open that file. New files or past files do not contain the code. I need those functions to be installed with the Apps and then available in each new or re-opened workbook.

    As I explained before, there are two ways, put your code in your Personal.xls(b) or in an Addin (and installed such that it automatically loads when Excel starts). Before giving more details about addins, are you familiar with th essential features of an Excel Addin, or which aspects are you still unclear about.

    So the questios are: How do I package the functions for installation and how do I install them to get the results that I want.

    Simply save the workbook as an addin. As I suggested before, do some simple research about Excel Addins, eg from Excel's help and a wealth of information you'll find with a quick search.

    And when I say install, I mean I will be using an installer to install the Apps.

    If you are using an installer to distribute your addin it will need to unpack it to a suitable folder, add the addin to Excel's Addin manager, and Install it such that it will automatically load when Excel starts. Depending on your installer app there will probably be a forum or link to info about how to distribute an Excel addin. But consider simply giving the user instructions how to manually install the addin (probably the majority of addins are distributed that way)

    I know about the XLSTART folder, but what goes in there??

    Your Personal.xls(b), possibly a custom default template (that loads every time you create a new workbook), and any other files you want to load automatically (though best not put addins there). For most people the folder would be empty or at most contain the Personal.xls. But as it seems you are wanting to distribute probably an addin is what you should be considering.

    Peter Thornton

    Monday, February 28, 2011 5:52 PM
  • Well, your replies meander in that they don't address the two very important points:

    I have to package the code and distribute it with an installer - at the same time that the Apps are installed. There is to be absolutely no action by the user in order to install the code.

    Once installed the code is to be part of any new documents or workbooks AND part of all past documents and workbooks.

    -----

    Perhaps it would help if you clarified the following:

    Why are we talking about "Personal" when I want the functions to be part of all new and past workbooks, for all users?

    [" (and installed such that it automatically loads when Excel starts)"] and ["add the addin to Excel's Addin manager,and Install it such that it will automatically load when Excel starts"]
    As far as I know, the Add-In Manager is run by the user to add or delete or disbale add-ins. But I want no user intervention. And yes, I want "it" to load automatically, but what is "it" and how or where do I install it so that it does load automatically? Your reply implies that there is more to it than just putting some kind of file in a certain folder.

    As for Add-Ins, I will research them again, but I have had little success in devising something that loads automatically without user intervention. Note that I have two XLSTART folders and saving a workbook with functions in both folders did not cause the functions to be in a new workbook.

    Monday, February 28, 2011 8:21 PM
  • I have created an add-in and saved it in the default location (C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns). I shut down Excel, and then re-opened it with a new workbook. I when to Tools> Add-Ins and the dialog box showed the add-in. But it's checkbox was empty. I need to have that automatically enabled/loaded after the file is placed into that folder.

    Now,  I had previously installed a a text-to-speech converter and Word functions/macros were installed as an Add-In and automatically loaded/enabled for each user, and for all new and past documents, so I know its possible to do that. I need to know how to do it for Word and Excel.

    Monday, February 28, 2011 8:54 PM
  • Well, your replies meander in that they don't address the two very important points:

    I appreciate you know what you want to do but you didn't describe it clearly, it makes it very difficult to give you the answers you are looking for. Read your OP again.

    I have to package the code and distribute it with an installer - at the same time that the Apps are installed. There is to be absolutely no action by the user in order to install the code.

    What installer are you using. As I mentioned before, there may be documentation associated with the installer that will instruct you how to install the addin (note the term "install" has a particular meaning with respect to Excel addins).

    After unpacking the addin there are two ways to "Install" it. The simplest involves these steps

    Ensure all instances of Excel are closed
    Automate a new instance of Excel.
    Add your addin to the Addins colelction
    Set the Addin's Installed property to true
    However with versioning (installing an update) there is more work to do.

    The other approach requires non trivial work with the registry, far too much to describe here.

    If you are not familiar with the above you have a learning curve. Do not dismiss the simple apprach I suggested earlier.

    Once installed the code is to be part of any new documents or workbooks AND part of all past documents and workbooks.

    Perhaps it would help if you clarified the following:

    Why are we talking about "Personal" when I want the functions to be part of all new and past workbooks, for all users?

    Your OP was a vague request about where to put code so that it will always be available. It would have been incomplete on my part not to explain to you about the Personal as one of the two appraches. It was only much later you said you were aiming to distribute your code, that was not clear at all in your earlier posts.

    [" (and installed such that it automatically loads when Excel starts)"] and ["add the addin to Excel's Addin manager,and Install it such that it will automatically load when Excel starts"]
    As far as I know, the Add-In Manager is run by the user to add or delete or disbale add-ins. But I want no user intervention. And yes, I want "it" to load automatically, but what is "it" and how or where do I install it so that it does load automatically? Your reply implies that there is more to it than just putting some kind of file in a certain folder.

    I get the impression you have a chance to read much about addins. Once again, let me suggest you look into these. If there is something you do not understand about how to create an addin post back. The other thing you need to understand is how Excel's Addin manager works.

    As for Add-Ins, I will research them again, but I have had little success in devising something that loads automatically without user intervention. Note that I have two XLSTART folders and saving a workbook with functions in both folders did not cause the functions to be in a new workbook.

    Forget about the XLSTART folder if you are using addins.

    Whilst you may use the same installer to distribute for both Word and Excel, they are two sub products of Office and the approaches are not the same. It would be easier for all concerned if in your postings you delineate.

    FWIW, with a number of languages, though not VBA, you can write a COMaddin that will work in both Word and Excel (and other Office apps).

    Peter Thornton

    Monday, February 28, 2011 10:01 PM
  • I have created an add-in and saved it in the default location (C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns). I shut down Excel, and then re-opened it with a new workbook. I when to Tools> Add-Ins and the dialog box showed the add-in. But it's checkbox was empty. I need to have that automatically enabled/loaded after the file is placed into that folder.

    Manually tick the box
    programatically, set a reference to your addin in the addins colelction and set its Installed property to True

    If the addin is in a default addins folder, sounds like it is, it will by default already exist in the addins collection, but you will still need to set its Installed property.

    However if it's elsewhere you need to add it to thed addins collection first, then set its installed property to True

    Note I'm only speaking about Excel addins.

    Peter Thornton

    Monday, February 28, 2011 10:06 PM
  • I have created an add-in and saved it in the default location (C:\Documents and Settings\Administrator\Application Data\Microsoft\AddIns). I shut down Excel, and then re-opened it with a new workbook. I when to Tools> Add-Ins and the dialog box showed the add-in. But it's checkbox was empty. I need to have that automatically enabled/loaded after the file is placed into that folder.

    Manually tick the box
    programatically, set a reference to your addin in the addins colelction and set its Installed property to True

    If the addin is in a default addins folder, sounds like it is, it will by default already exist in the addins collection, but you will still need to set its Installed property.

    However if it's elsewhere you need to add it to thed addins collection first, then set its installed property to True

    Note I'm only speaking about Excel addins.

    Peter Thornton

    When does this manual ticking take place? when Excel is opened again after the add-in is saved?

    What is the "Installed property" and do I really need to write code to set it?

    I gave you the default add-ins folder as far as I know it. Its obviously for the Administrator, and not for all users. Where else would it go?

    Yes, I understand that you speak for Excel only.

    Monday, February 28, 2011 10:59 PM
  • Well, your replies meander in that they don't address the two very important points:

    I appreciate you know what you want to do but you didn't describe it clearly, it makes it very difficult to give you the answers you are looking for. Read your OP again.

    I have to package the code and distribute it with an installer - at the same time that the Apps are installed. There is to be absolutely no action by the user in order to install the code.

    What installer are you using. As I mentioned before, there may be documentation associated with the installer that will instruct you how to install the addin (note the term "install" has a particular meaning with respect to Excel addins).

    After unpacking the addin there are two ways to "Install" it. The simplest involves these steps

    Ensure all instances of Excel are closed
    Automate a new instance of Excel.
    Add your addin to the Addins colelction
    Set the Addin's Installed property to true
    However with versioning (installing an update) there is more work to do.

    The other approach requires non trivial work with the registry, far too much to describe here.

    If you are not familiar with the above you have a learning curve. Do not dismiss the simple apprach I suggested earlier.

    Once installed the code is to be part of any new documents or workbooks AND part of all past documents and workbooks.

    Perhaps it would help if you clarified the following:

    Why are we talking about "Personal" when I want the functions to be part of all new and past workbooks, for all users?

    Your OP was a vague request about where to put code so that it will always be available. It would have been incomplete on my part not to explain to you about the Personal as one of the two appraches. It was only much later you said you were aiming to distribute your code, that was not clear at all in your earlier posts.

    [" (and installed such that it automatically loads when Excel starts)"] and ["add the addin to Excel's Addin manager,and Install it such that it will automatically load when Excel starts"]
    As far as I know, the Add-In Manager is run by the user to add or delete or disbale add-ins. But I want no user intervention. And yes, I want "it" to load automatically, but what is "it" and how or where do I install it so that it does load automatically? Your reply implies that there is more to it than just putting some kind of file in a certain folder.

    I get the impression you have a chance to read much about addins. Once again, let me suggest you look into these. If there is something you do not understand about how to create an addin post back. The other thing you need to understand is how Excel's Addin manager works.

    As for Add-Ins, I will research them again, but I have had little success in devising something that loads automatically without user intervention. Note that I have two XLSTART folders and saving a workbook with functions in both folders did not cause the functions to be in a new workbook.

    Forget about the XLSTART folder if you are using addins.

    Whilst you may use the same installer to distribute for both Word and Excel, they are two sub products of Office and the approaches are not the same. It would be easier for all concerned if in your postings you delineate.

    FWIW, with a number of languages, though not VBA, you can write a COMaddin that will work in both Word and Excel (and other Office apps).

    Peter Thornton

    We are well past the OP. It seems we use different terms or that they have different meaning to us. So it seems to you that I'm not explaining myself, but I'm quite sure I am and don't know how else to explain it. On the other hand - and don't take offence - you may not know the answer in the detaisl that I need.

    Installer: I will use whatever comes with VC# 2010 Express. But if "install" has a particular meaning with respect to Excel addins, then what should I be using? something free, please. What is difference between a normal installer and one that installs Add-Ins?

    "Automate a new instance of Excel." Does this mean Office Automation code in, say in C#? If so, be aware that such code causes all kinds of complications, and is not an option.

    Your installation process again includes manual intervention, does it not? See below?
    "Add your addin to the Addins colelction
    Set the Addin's Installed property to true"

    I've read enough about Add-Ins, creating them, and about the Add-In Manager. If I am missing something why not tell me; then I don't have to guess.

    Monday, February 28, 2011 11:20 PM
  • On the other hand - and don't take offence - you may not know the answer in the detaisl that I need.

    I don't take offence but your approach is not conducive to gaining you the help you are looking for. But lets move on.

    Installer: I will use whatever comes with VC# 2010 Express. But if "install" has a particular meaning with respect to Excel addins, then what should I be using? something free, please. What is difference between a normal installer and one that installs Add-Ins?

    A generic Installer may do all sorts of things as instructed by the developer, eg

    - Check to see if your old app exists, if necessary uninstall it completely or write over it
    - Unpack files to correct location (which may vary depending on OS, single/multiple users, OS version, etc)
    - Check the necessary system requirements exists, and/or prexisting apps (eg Excel and which version)
    - Add/delete necessary registry settings (might involve checking other reg settings)
    - Any other work, in this case maybe automation of Excel to "install" the addin

    Essentually an Installer is an application. How to instruct it varies from say writing a C like script to completing a UI of options. Most comprehensive installers will be able to do the work of installing an Addin (automate Excel etc or registry work). I haven't used the VC Express installer and not sure it will give you the particular flexibility required to Install an Excel addin (not saying it won't)

    One installer that does have the flexibility is INNO
    http://www.jrsoftware.org/isinfo.php

    Somewhere on the site or in the forums history there are sample scripts or templates for use with installing Excel addins.

    Instead of a full installer you could distribute another workbook to unpack and "Install" the addin see "SetupUtlity.zip" on this page
    http://www.jkp-ads.com/Download.asp

    On the same site this page will explain more to you abut addins
    http://www.jkp-ads.com/Articles/DistributeMacro00.asp
     > "Automate a new instance of Excel." Does this mean Office Automation code

    in, say in C#? If so, be aware that such code causes all kinds of complications, and is not an option.

    As I have tried to explain, programatically (ie with your installer) there are only two ways to "Install" an addin. You say Automation is not an option so that only leaves you with the much harder (in terms of learning and coding) route of registry work (more than simply adding one or two settings). Not sure what experience you have with Automation on which to base a statement like "...such code causes all sorts of problems". Excel is designed to be automated.

    In passing, if you want to cater for users with multiple Excel versions on same system, the registry approach is the only way. Except...ask the user to install it!

    Your installation process again includes manual intervention, does it not? See below?
    "Add your addin to the Addins colelction
    Set the Addin's Installed property to true"

    I've read enough about Add-Ins, creating them, and about the Add-In Manager. If I am missing something why not tell me; then I don't have to guess.

    It seems you are missing what what the term "Install" means with particular respect to an addin, I have tried to explain. It means first ensuring the addin is added to the Excel object model's addin's collection, then ensuring its Installed property is True (so it will automatically load)

    If the addin is in a desginated addins folder, it is automatically added to the addins collection. Otherwise it must be indicated in a particular registry location (for uninstalled addins). Behind the scenes the act of Installing the addin (manually or programatically) adds a different registry setting, that's seen by Excel when it starts, in turn automatically loading the addin. Manually ticking the box in the addin manager's dialog does that as a one off operation. Similar can be replicated with Automation. The alternative is a complicated registry hack.

    Peter Thornton

    Tuesday, March 01, 2011 9:49 AM
  • I've explained all this in more detail in my adjacent post (a few minutes ago)

    When does this manual ticking take place? when Excel is opened again after the add-in is saved?

    What is the "Installed property" and do I really need to write code to set it?

    If not ticked manually the yes, you need to write code, either to automate Excel and replicate the manual procedure or to replicate what goes on behind the scenes in the registry.

    "Installing" the addin is a one off procedure, unless of course the user later uninstalls it. However providing he doesn't remove it from file it will be a simple matter for hime to re-install it.

    I gave you the default add-ins folder as far as I know it. Its obviously for the Administrator, and not for all users. Where else would it go?

    No, the default addins folder is accessible by the user. You can get it with a reference to the object model, eg in the Immediate window
    ?application.UserLibraryPath

    However the addin can go in any folder that has read (and if necessary write) permissions of the user.

    Peter Thornton

    Tuesday, March 01, 2011 10:00 AM
  • @Peter Thronton

    Sorry, I am done with this thread. You tried your best, but its clear we are getting nowhere.

    Thanks anyway.

    Tuesday, March 01, 2011 3:12 PM