JKP Application Development Services.

                    Microsoft Office Application Development

Installing An Excel Add-in Using Setup Factory

Introduction

Being an MVP has some benefits. One of them is that some software companies offer Not For Resale (NFR) products to them for free. Of course these companies hope the MVP's will advocate their products.

Well, for this one I will do so:

I've started using this tool just a week ago and I must say I'm most impressed with it.

Some Excel background first!

How Add-ins are Managed in Excel

In Excel, all the user sees is the list of available Add-ins when he selects Tools, Add-ins. Here is what my Add-ins dialog looks like:

Tools, Add-ins list of Excel

What Add-ins are available

Under the hood, Excel keeps score in the registry which Add-ins there are and which are installed.

To build the list in the above dialog, Excel looks in a couple of places:

1. The Add-ins folder:

C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns

or altenatively:

C:\Program Files\Microsoft Office\OFFICE11\Library

This is for Excel 2003, for Excel XP this would be:

C:\Program Files\Microsoft Office\OFFICE10\Library

Any Add-ins in this folder are automatically included in the Add-ins dialog.

2. The registry, in this location:

For Add-ins in a different location from the ones shown above, Excel will look in the registry. Keys are added there when you click the browse button to locate an Add-in.

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Add-in Manager

So for Excel XP this is:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Add-in Manager

In this location, there is a value for each Add-in to be shown in the Add-ins dialog. The value needed is simply the path to the Add-in, e.g.:

C:\Program Files\Autosafe\autosafe.xla

Part of the registry that handles Add-ins that are not in the default location

Which Add-ins are Selected

In another location in the registry, Excel notes what Add-ins are selected. It does so using a number of values in this part of the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options

For each selected Add-in,  Excel adds an entry in this location, successively called "OPEN", "OPEN1", "OPEN2",...

Registry showing entries for Add-ins that are selected

How To Install An Excel Add-in using Setup Factory

Setup factory does not come with an automatic/wizard driven way to handle the registry entries that need to be produced for Excel, so an Add-in is properly added to the Add-ins dialog and is installed. But it does come with a versatile scripting language that enables you to program these entries yourself quite easily.

I've spent maybe 4 hours to figure this out and I had never used the scripting bit of Setup Factory before. Note that only the registry part needs scripting, the remaining stuff (creating the setup screens, configuring how the setup works, where to install your files, what files to install...) is done through easy to use wizards and configuration screens.

Script for registry modifications during Install of an Add-in

I've written the script shown below which does the following:

The script assumes you have created a -what Setup Factory calls- "Custom Session variable", named "AddinFileName", which contains the filename of your addin.

versions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

openindex={}

sFilename=SessionVar.Expand("%AddinFileName%");

for Count1,Version in versions do

    result = Registry.GetValueNames(2, "Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options");

    openindex[Count1]=-1

    bFound=false

    if result then

        for Count2,Value in result do

            if not bFound then

                if (String.Left(Value,4)=="OPEN") then

                    if String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))>openindex[Count1] then

                        sKey=Registry.GetValue(2,"Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options",Value,true)

                        if String.Find(sKey,sFilename,1,false)>0 then

                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))

                            bFound=true

                        else

                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))

                        end;

                    end;

                end;

            end;

        end;        

        sAppPath=SessionVar.Expand("%AppFolder%").."\\"..sFilename

        sKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options\\"

        sAddinmgrKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Add-in Manager\\"

        AddinMgrList=Registry.GetValueNames(2,sAddinmgrKey)

        if AddinMgrList then

            for Count1, AddinmgrValue in AddinMgrList do

                if String.Find(AddinmgrValue,sFilename,1,false) then

                    Registry.DeleteValue(2,sAddinmgrKey,AddinMgrList[Count1])

                end;

            end;

        end;

        Registry.SetValue(2,sAddinmgrKey,sAppPath,"",REG_SZ)

        if bFound then

            if openindex[Count1]==0 then

                Registry.SetValue(2,sKey,"OPEN",String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)

            else

                Registry.SetValue(2,sKey,String.Concat("OPEN",openindex[Count1]),String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)

            end;

        else

            if openindex[Count1]==0 then

                Registry.SetValue(2,sKey,"OPEN",String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)

            else

                Registry.SetValue(2,sKey,String.Concat("OPEN",openindex[Count1]+1),String.Concat(String.Concat(String.Char(34),sAppPath),String.Char(34)),REG_SZ)

            end;

        end;

    end;

end;

To make sure no registry entries are changed until all files have been installed successfully, make sure you add this script to the "On Post Install" action in Setup factory.

How to uninstall an addin

Of course Setup Factory also generates an uninstall method, both in the start menu and in the Add/remove programs applet of Windows Control Panel.

Again, some registry changes are necessary because the proper Add-in Manager entry and the Options/OPEN entry have to be removed from the registry in order to prevent startup errors during load of Excel.

For this I wrote the script shown below, to be run at "On Post Uninstall".

It does the following:

versions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

openindex={}

sFilename=SessionVar.Expand("%AddinFileName%");

for Count1,Version in versions do

    result = Registry.GetValueNames(2, "Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options");

    openindex[Count1]=-1

    bFound=false

    if result then

        for Count2,Value in result do

            if not bFound then

                if (String.Left(Value,4)=="OPEN") then

                    if String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))>openindex[Count1] then

                        sKey=Registry.GetValue(2,"Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options",Value,true)

                        if String.Find(sKey,sFilename,1,false)>0 then

                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))

                            bFound=true

                        else

                            openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))

                        end;

                    end;

                end;

            end;

        end;        

        sAppPath=SessionVar.Expand("%AppFolder%").."\\"..sFilename

        sKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options\\"

        sAddinmgrKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Add-in Manager\\"

        AddinMgrList=Registry.GetValueNames(2,sAddinmgrKey)

        if AddinMgrList then

            for Count1, AddinmgrValue in AddinMgrList do

                if String.Find(AddinmgrValue,sFilename,1,false) then

                    Registry.DeleteValue(2,sAddinmgrKey,AddinMgrList[Count1])

                end;

            end;

        end;

        if bFound then

            if openindex[Count1]==0 then

                Registry.DeleteValue(2,sKey,"OPEN")

            else

                Registry.DeleteValue(2,sKey,"OPEN"..openindex[Count1])

            end;

        end;

    end;

end;

Feedback

Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me.

You may also rate this article:

Comments

Add a comment too!!!

    Subscribe in a reader

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2008 JKP Application Development Services