Installing An Excel Add-in Using Setup Factory

Content

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: Setup Factory - Software Installation Builder for Windows

I must say I'm most impressed with it.

Some Excel background first!

Thank you note

Special thanks to Emiel Nijhuis, for fine-tuning the script!

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:

Add-ins list of Excel
Add-ins list of Excel

You can navigate to this dialog as follows:

  • Excel 2007: Start-button , Excel Options, Add-Ins, drop down: Excel add-ins, Go
  • Excel 2010: File tab, Options, Add-Ins, drop down: Excel add-ins, Go

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

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

and etcetera.

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

2. The registry

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 2007 this is:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.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
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
Registry showing entries for Add-ins that are selected

These keys each contain the name of the add-in to be opened (and sometimes some command-line parameters). If an add-in is not in the add-ins folder the full path is included.

Note that the Keys are updated AFTER closing Excel.

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

On Startup script

It is a good idea to check whether Excel has been closed before installing your addin, otherwise the addin will not be installed (because Excel will update the registry upon closing, it will remove the keys just added by the script). So add this On startup script:

--Check whether Excel is opened.
sValues = Window.EnumerateTitles(true);

--Next line has SetupFactory 8 code
--for iCount, sValue in sValues do
for iCount, sValue in pairs(sValues) do
    if String.Find(sValue, "Microsoft Excel", 1, true)>0 then 

        Dialog.Message("Error", "First close Microsoft Excel."..String.Char(10)..String.Char(10).."Installer will now abort.",MB_OK, MB_ICONINFORMATION);
        Application.Exit(0);
    end;
end;

On post install script

The script shown below does the following:

  • Find out which Excel versions are present in the system by reading what numeric keys are present in this registry location:
    HKEY_CURRENT_USER\Software\Microsoft\Office
    (Each version has a numeric entry here: 12.0 for 2007, 14 for 2010, 15 for 2013, 16 for Excel 2016, 2019, 2021 and 365.)
  • For each of these versions the script then looks in the Excel/Options part of the registry whether the addin-to-be-installed is already listed there. If so, the key is noted and removed, but will be recreated later on in he script.
  • After that, the same is done for the entries in the Addin-manager location of the registry: look if the addin-to-be-installed is present and if so, remove the Value from the registry.
  • Now that old versions are removed from the registry, the script can add the values and keys for the newly installed add-in.

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

-- Determine registry key (2 = HK CURRENT USER)
sVersions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

-- Iterate through the registry keys per MS Office-version
--Next line has SetupFactory 8 code
--for iCount1, sVersion in sVersions do   
for iCount1, sVersion in pairs(sVersions) do   

    -- Try opening the registry key
    sSubKey = "Software\\Microsoft\\Office\\"..sVersion..
              "\\Excel\\Options\\"
    sValues = Registry.GetValueNames(2, sSubKey);

    --initialize index counter
    iIndex = -2
    if sValues then

        --Determine the index of the maximimum OPEN registry entry
        --Next line has SetupFactory 8 code
        --for iCount2, sValue in sValues do
        for iCount2, sValue in pairs(sValues) do

            if (String.Left(sValue, 4) == "OPEN") then           
                --Check whether the user did not already install
                --the same add-in to prevent errors when opening Excel
                sKeysValue = Registry.GetValue(2, sSubKey, sValue, true) 
                if String.Find(sKeysValue, SessionVar.Expand(
                              "%AddinFileName%"), 1, false) > 0 then
                    iIndex = -1
                    -- leave loop
                    break;
                else
                    if (sValue == "OPEN") then
                        iIndex = 0
                    else
                        iIndex = String.ToNumber(String.Mid(
                                 sValue, 5, String.Length(sValue)-4))
                    end;
                end;
            end;
        end;

        -- -1 means: This add-in is already installed; we're done
        if iIndex ~= -1 then       
            --Determine path based on variable "%AddinFileName%
            sAppPath = String.Char(34)..
                       SessionVar.Expand("%AppFolder%")..
                       "\\"..
                       SessionVar.Expand("%AddinFileName%")..
                       String.Char(34)

            -- -2 is the initialized value of the index counter
            if (iIndex == -2) then
                -- OPEN-key does not exist
                Registry.SetValue(2, sSubKey, "OPEN",
                                  sAppPath, REG_SZ)
            else
                Registry.SetValue(2, sSubKey, "OPEN"..(iIndex + 1),
                                  sAppPath, 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 add-in

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.

On Startup

Again, the following script is optional if you want the user to close Excel before uninstalling your add-in:

--Check whether Excel is opened.
sValues = Window.EnumerateTitles(true);

--Next line has SetupFactory 8 code
--for iCount, sValue in sValues do

for iCount, sValue in pairs(sValues) do
    if String.Find(sValue, "Microsoft Excel", 1, true)>0 then                  
        Dialog.Message("Error", "First close Microsoft Excel."..
                        String.Char(10)..String.Char(10)..
                        "Uninstaller will now abort.",
                        MB_OK, MB_ICONINFORMATION);
        -- Make sure the process ends with a value other than 0
        -- so the uninstall can be performed again.
        Application.Exit(EXIT_REASON_USER_ABORTED);
    end;   
end;

On Post Uninstall

It does the following:

  • Find out which Excel versions are present in the system by reading what numeric keys are present in this registry location:
    HKEY_CURRENT_USER\Software\Microsoft\Office
    (Each version has a numeric entry here: 9.0 for Excel 2000, 10.0 for XP and so on.)
  • For each of these versions the script then looks in the Excel/Options part of the registry whether the addin-to-be-uninstalled is listed. If found, the key is removed.
  • After that, the same is done for the entries in the Addin-manager location of the registry: look if the addin-to-be-uninstalled is present and if so, remove the Value from the registry.
     
-- Determine registry key (2 = HK CURRENT USER)
sVersions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

-- Iterate through the registry keys per MS Office-version

--Next line has SetupFactory 8 code
--for iCount1, sVersion in sVersions do

for iCount1, sVersion in pairs(sVersions) do
    -- Try opening the registry key
    sValues = Registry.GetValueNames(2,
    "Software\\Microsoft\\Office\\"..sVersion.."\\Excel\\Options");

    if sValues then

--Next line has SetupFactory 8 code
--        for iCount2, sValue in sValues do

        for iCount2, sValue in pairs(sValues) do

            -- Any installed add-ins present in this Office version?
            if (String.Left(sValue, 4) == "OPEN") then

                sSubKey = "Software\\Microsoft\\Office\\"..sVersion..
                          "\\Excel\\Options\\"
                sKeysValue = Registry.GetValue(2, sSubKey, sValue, true)

                -- Delete the registry key if we encounter our add-in
                if String.Find(sKeysValue, SessionVar.Expand(
                               "%AddinFileName%"), 1, false)>0 then
                    --Dialog.Message(sSubKey, sValue) –-*for debugging*
                    Registry.DeleteValue(2, sSubKey, sValue)
                end;
            end;
        end; 
    end;
end;

 

Comments

All comments about this page:

Comment by: Durlabh Jain (17-4-2007 15:02:56) deeplink to this comment

Thanks for a great article. This was a big help for my excel excel add-in installation script in Inno-Setup. In case you are interested in taking a look at it, script is available at http://durlabh.blogspot.com/2007/04/using-inno-setup-for-excel-add-in.html

Comment by: Jim Johnson (17-5-2007 02:34:31) deeplink to this comment

It's useful to read your approach to this, since I'm working on improving my setup script.

A related issue that I found was that some installations require administrator permissions, which the user may not have. If the installer only handles keys under HKCU and someone else installs the add-in for the user then the user will not see the add-in.

Comment by: AndyGreek (12-6-2007 03:06:40) deeplink to this comment

Nice site!
a pleasant suprise

Comment by: Al Benton (27-7-2007 10:56:17) deeplink to this comment

Hello,

I looked in the registry for entries when an Add-in is manually installed from the pull-down menu. I expected an "Open1" entry but none exists. The reason for my comment is that I have a VBA script in my add-in to remove itself when the user exits Excel. For some reason it crashes Excel. I was hoping your registry-based approach would solve my problem. Any comments?

Here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Integer
    
    On Error Resume Next
    
    For i = AddIns.Count To 1 Step -1
        If UCase$(AddIns(i).Name)= "MyApp.XLA" Then
            AddIns(i).Installed = False
            Exit For
        End If
    Next i    
End Sub

Comment by: Al Benton (27-7-2007 12:20:02) deeplink to this comment

Hello,

I commented earlier on a problem with exiting add-ins crashing Excel. It appears that the problem only happens when I manually install the add-in from the pulldown menu. If I start the add-in by double-clicking on it from file manager before (or after) opening my Excel data file everything exits correctly (obviously the workbook close logic is not needed since nothing was installed). That tells me that Excel, on exiting, does the writes to the registry that your article describes. This conflicts somehow with the workbook close logic I was using.

Comment by: Jan Karel Pieterse (1-8-2007 22:10:59) deeplink to this comment

Hi Al,

You're correct, the changes you make to addins by using Tools, addins are only saved to the registry after you quit Excel. This is why -from VBA- you have to use the addins collection object to work with addins, rather than manipulate the registry.

Comment by: lukman chakim (24-8-2007 01:56:29) deeplink to this comment

thanks for this great article :)

Comment by: hiutopor (17-9-2007 12:49:27) deeplink to this comment

Hello

Very interesting information! Thanks!

G'night

Comment by: lokimikoj (22-9-2007 01:19:53) deeplink to this comment

Hi

Very good site! I like it! Thanks!

Comment by: bill (12-10-2007 03:54:22) deeplink to this comment

I am not able to use this script ,please provide some additional guidance for same.Description About variable used

Comment by: Jan Karel Pieterse (12-10-2007 07:24:58) deeplink to this comment

Hi Bill,

Could you tell me the error message?

Comment by: Jeroen Vreman (15-10-2007 01:00:56) deeplink to this comment

Hello,

Thanks for this great article. This was a big help for me to create my own setup file.

I found a small bug in both (install/uninstall) scripts.

The first add-in in the registry is defined in "OPEN". In the above install script the first add-in will be installed in "OPEN0"

If you uninstall the add-in then you should re-order the registry "OPEN" numbers. Especially as you install an another add-in. Otherwise a gap is created. Excel re-order this automatically when you open excel add-in menu and click [ok] and restart excel, but this is not user friendly.

The scripts are to big (2300 char) to add as comment. Therefor a part is added, see next comments.

Regards, Jeroen Vreman

Comment by: Jeroen Vreman (15-10-2007 01:03:08) deeplink to this comment

The part of my install script what I have changed:

    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
                 openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))
                else
                 openindex[Count1]=0
                end;
                
                sKeyFind=Registry.GetValue(2,"Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options",Value,true)
                
                if String.Find(sKeyFind,sFilename,1,false)>0 then
                 bFound=true
                end;
                end;
            end;
        end;        

sAppPath=SessionVar.Expand("%AppFolder%").."\\"..sFilename
        sKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options\\"
        
        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]==-1 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;
        
        sAddinmgrKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Add-in Manager\\"

Comment by: Jeroen Vreman (15-10-2007 01:04:25) deeplink to this comment

The part of my uninstall script what I have changed:

    if result then
    
        for Count2,Value in result do
    
         if (String.Left(Value,4)=="OPEN") then
            
                if String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))>openindex[Count1] then
                    openindex[Count1]=String.ToNumber(String.Mid(Value,5,String.Length(Value)-4))
                else
                 openindex[Count1]= 0
                end;
                
                sKey="Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options\\"    
                        
        if bFound then
sKeyValue=Registry.GetValue(2,sKey,String.Concat("OPEN",openindex[Count1]),true)
    
    if openindex[Count1]==1 then
         Registry.SetValue(2,sKey,"OPEN",sKeyValue,REG_SZ)
Registry.DeleteValue(2,sKey,"OPEN"..openindex[Count1])
     else
         Registry.SetValue(2,sKey,String.Concat("OPEN",openindex[Count1]-1),sKeyValue,REG_SZ)
Registry.DeleteValue(2,sKey,"OPEN"..openindex[Count1])
     end;
else
sKeyFind=Registry.GetValue(2,"Software\\Microsoft\\Office\\"..Version.."\\Excel\\Options",Value,true)
                
                 if String.Find(sKeyFind,sFilename,1,false)>0 then
    bFound=true
    if openindex[Count1]==0 then
         Registry.DeleteValue(2,sKey,"OPEN")
     else
         Registry.DeleteValue(2,sKey,"OPEN"..openindex[Count1])
     end;                    
                    end;
                end;
         end;
        end;        

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

Comment by: Jan Karel Pieterse (15-10-2007 07:19:05) deeplink to this comment

Hi Jeroen,

Thanks a lot for your enhancements, I'll check this out.

Comment by: JamesCooks (24-11-2007 10:35:44) deeplink to this comment

Nice.
Keep up the great work.

Comment by: Jeroen Vreman (7-12-2007 04:41:44) deeplink to this comment

If you want to install an add-in for all users then use the script below.
The script create a shortcut to your add-in file in the XLStart folder.
The add-in will be opened when Excel is starting.

Regards,
Jeroen Vreman

Install script:
-- find XLStart folder in microsoft office version(s)
sAppPath=SessionVar.Expand("%AppFolder%").."\\"..SessionVar.Expand("%AddinFileName%")
sMsOfficePath=SessionVar.Expand("%ProgramFilesFolder%").."\\Microsoft Office"
    
result = Folder.Find(sMsOfficePath,"XLStart",true,"");

-- create a shortcut of the add-in file in XLStart folder(s)
if result then
for Count1,Value in result do
Shell.CreateShortcut(Value,"Add-In Tools",sAppPath);
end;
end;

UnInstall script:
-- find XLStart folder in microsoft office version(s)
sMsOfficePath=SessionVar.Expand("%ProgramFilesFolder%").."\\Microsoft Office\\"
    
result = Folder.Find(sMsOfficePath,"XLStart",true,"");

-- delete the shortcut(s) of the add-in file in XLStart folder(s)
if result then
for Count1,Value in result do
Shell.DeleteShortcut(Value,"Add-In Tools")
end;
end;

Delete User Settings in registry key 'VB and VBA Program Settings' for all users:
-- delete user settings if selected
nSelectedControl = String.ToNumber(SessionVar.Expand("%UserSettings%"));
if (nSelectedControl == CTRL_RADIO_BUTTON_01) then

sKey = "SYSTEM\\ControlSet001\\Control\\hivelist\\"
result = Registry.GetValueNames(3,sKey);

for Count1,Value in result do
Value = String.Right(Value,String.Length(Value)-10);

if (String.Left(Value,4) == "USER") then
Value = String.Right(Value,String.Length(Value)-5);

sKey = Value.."\\Software\\VB and VBA Program Settings\\"..SessionVar.Expand("%ProductName%").."\\";
Registry.DeleteKey(4,sKey);
end;
end;
end;

Comment by: Ivan (6-2-2008 16:05:16) deeplink to this comment

thanks for letting me view your guest book and giving me all the information

Comment by: swapnil (26-3-2008 14:02:25) deeplink to this comment

Hi Jan.
Thanks for the imformative article. If I want to add an addin from a network drive without actually copying it to the local pc then how would I do that with the script?
Thanks again.

Comment by: Jan karel Pieterse (27-3-2008 08:17:49) deeplink to this comment

Hi Swapnil,

I assume you're using SUF for this. In the Setup factory session variables you enter the right destination path for the add-in. But I think you'd have to do this differently, since your setup program does not have to copy the addin to the network location each time (just once), it just has to set the proper registry entries.

You need two steps:

1. Copy xla to network folder (set its property to readonly, so you can easily replace it when needed)
2. Create logon script for users that sets the registry entries.

Comment by: ToniKiguige (22-10-2008 00:40:58) deeplink to this comment

Hello guys, I'm new to this web site. I never realized there are such active communities on the internet. I like what's shared here by looking at the first few pages. I hope to share some of my past experiences.

Thanks for reading.
_______________________________________
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

Comment by: Rubberduckone (12-11-2008 01:05:14) deeplink to this comment

Hi Jan
Appreciate the informative, short and precise article - excellent!

Looking at my registry, to see what is actually set as 'Selected' (the 'Options' key)

I wonder... - some of the Data have a parameter in front - like /F "C:\Program Files\... or \R "C:\Program Files\...

Do you know what the parameters mean and how many are there??

TIA

Comment by: Jan Karel Pieterse (12-11-2008 01:37:04) deeplink to this comment

Hi Rubberduckone,

No, not quite. I suspect it may have something to do with whether or not the addin should fully load, or just load it's userinterface and then only fully load when any option of the addin is clicked.

Comment by: Golden Child (22-12-2009 09:10:52) deeplink to this comment

Great Artice, but 1 question if for example I have 3 excel addins and 1 is published to all users and the other 2 are set via security groups integrated via another application like powerfuse how would this work....i.e. as you need to do the open in order other it wont work so e.g.

1 - All user addin = Open
2 - Finance Addin = Open1
3 - IT addin = Open2

So by default the user will get open, then they get the IT addin but because its jumping from open to open2 this wont work please correct me if I am wrong and any guidance on how to implement something similar to the above would be awesome.

Best wishes

Comment by: Jan Karel Pieterse (22-12-2009 10:52:02) deeplink to this comment

Hi Golden Child,

I'm not entirely sure what your question is, but if it is in what order the add-ins are loaded, then I guess it is in the order as they are listed in the list: Open, Open1, Open2.

But I haven't actually tested this.

Comment by: Golden Child (23-12-2009 02:05:00) deeplink to this comment

Hi Jan,

Thanks for getting back to me, basically if I have 3 excel addins and i name them open, open 1, open 2 my understanding is this would be fine if all the users have all 3 addins but what if a user has addin open and open2 and not open 1 would the addin fail because its not going in order?

I have created 3 addins and have 3 AD security groups which I am going to delegate my addins based on but the above is giving me some confusion...Hope this makes sense and thank you for your time

Comment by: Jan Karel Pieterse (23-12-2009 04:15:00) deeplink to this comment

In order to make things work smoothly, I suggest to keep the numbers in the registry ascending: Open, Open1, Open2, ...
Note that these are the registry key names, NOT the names of the addins themselves!

Comment by: Stein Magne Nilsen (21-1-2010 08:32:46) deeplink to this comment

Every time I open my excel worksheets and from different computers I need to update links to the location on my add-in file.
I have stored my add-in on c:\ on both computers.
I thought the add-in setup should handle even different locations on add-ins??

Comment by: Jan Karel Pieterse (21-1-2010 08:50:28) deeplink to this comment

Hi Steine,

Have a look at this page:

https://jkp-ads.com/Articles/FixLinks2UDF.asp

Comment by: Stein Magne Nilsen (2-2-2010 22:45:09) deeplink to this comment

I've had problems reading values from formula cells in vba by using references to the sheet like this:
ActiveSheet.Range("A:E").Cells(3,5). Where Column 5 is formula cells. It works when I use direct reference like this: ActiveSheet.Range("E5") but that is more difficult to handle i code.
When I debug and stop the code, I see that all the cells in that column return Empty value. In the Immediate window I can read the value though like this:
? ActiveSheet.Range("A:E").Cells(3,5), but not in the debug window.

I've tried to run Application.CalculateFull before reading the range, but no respons. It actually works when I edit the formula cells by dragging and copy the formula in that column. But if I edit other cells after that, they return Empty again in code. I've tried this on excel 2003 on several computers.

Comment by: Jan Karel Pieterse (3-2-2010 00:12:23) deeplink to this comment

Hi Stein,

Maybe you need to add the Value property:

ActiveSheet.Range("A:E").Cells(3,5).Value


Also, why add the range, since your range starts in cell
A1, this should be the same:

ActiveSheet.Cells(3,5).Value

Comment by: Stein Magne Nilsen (3-2-2010 05:11:08) deeplink to this comment

Thanks Jan Karel.

I've tried to add .value, but no response.
I use Range as parameter in the function. I need the Range reference to make it flexible.
It's strange that I can read the value from the formula cells in the immediate window, and not in the code window while debugging.

Comment by: Jan Karel Pieterse (3-2-2010 06:24:21) deeplink to this comment

Hi Stein,

Is your function by any chance called from a worksheet cell?

Maybe you could email me the file and explain what it does not do that you want done?

Comment by: Stein Magne Nilsen (3-2-2010 09:19:32) deeplink to this comment

It is called from a worksheet cell.
I will email you a part of the code where the problem is.

Comment by: Dutch (22-3-2010 08:28:27) deeplink to this comment

The registry key

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


is only used by Excel (here 2003) when the inactive Add-In is residing on the *same* drive but in a different folder, i.e. not the "Application.UserLibraryPath" nor in the "Application.LibraryPath" properties (both from Excel, see point 1. at the top of the page)

If the Add-In is listed in key

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options


then it represents an active Add-In in one of the standard Add-In folders (no path) or in a custom folder (with path).

Generally, Excel always tries to copy the Add-In to the user's Add-In folder if the Add-In is located on a different drive.

The "OPENx" key may contains a drive/path indicator to the XLA(M).

When uninstalling via code (Setup Factory or InnoSetup) you need to remove the registry value with the full name located under

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


If the Add-In is located in one of the 2 standard Add-In folders, it is sufficient to physically remove the XLA(M) file with *Excel closed* (very important).

The next time Excel starts, it automatically clears all "OPENx" values pointing to such deleted (missing) XLA(M) files.

Dutch

Comment by: Jan Karel Pieterse (22-3-2010 08:35:45) deeplink to this comment

Hi Dutch,

If you read between the lines in my article, I do explain that is the way it works. Thanks for clarifying.

Comment by: Arijeet (7-7-2010 09:10:26) deeplink to this comment

This script is excellent. I want a simple modification. How do I add a /R to sAppPath while writing it into the Options in the Registry? This /R would ensure that the .xlam is loaded for the very first time also.

Comment by: Jan Karel Pieterse (8-7-2010 11:22:31) deeplink to this comment

Hi Arijet,

I suppose like this:

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

If it doesn't work, maybe you have to use two slashes:
" // R"

Comment by: Ben Logan (16-7-2010 01:17:09) deeplink to this comment

You missed the fact that add-ins can also be loaded/sourced from the xlstart area, e.g.

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

There are, unfortunately, many ways in which you can load an addin - if anyone is aware of any website/article that discusses load order/priority/sequence then that would be much appreciated!?

Comment by: Jan Karel Pieterse (16-7-2010 06:30:26) deeplink to this comment

Hi Ben,

I did not miss that fact.
I deliberately omitted it because -in my opinion- Add-ins do not belong in xlstart because that makes it hard for a user to turn them on or off.

Comment by: DK (29-11-2010 19:22:42) deeplink to this comment

As greatful as I am for your scripts,
I'm still having a hard time creating an installation file because I can't figure out where to create the "AddinFileName" variable and enter the registry modification code.
Would it be possible for you to give a brief step by step instruction for newbies like myself?

Comment by: Jan Karel Pieterse (29-11-2010 23:20:42) deeplink to this comment

Hi DK,

Sure.

First you click "Session Variables" and then click the Add button.
Name: %AddinFileName%
Value: The filename of your addin, for example: Autosafe.xla

OK.

Next, click the On post install link in the Actions category. That is where the first code goes.

Finally, from the menu, pick "Uninstall", "Actions" and then on the Post uninstall tab, paste the second piece of code.

Comment by: Gaetan (4-12-2010 19:11:40) deeplink to this comment

Hi,

I have two questions.

1/ Using setup factory, how can we close excel, so that we can replace the .xla with a new version?

2/ If we install the add in in a specific directory (e.g. www.program files/nameofsoftware), is it an obligation to have the .xla file also in the add-in directory ?

Thank you for your answers.

Gaetan

Comment by: Jan Karel Pieterse (6-12-2010 00:49:13) deeplink to this comment

Hi Gaetan,

On your questions:

1. Maybe you can, but I wouldn't know the script to do that. One thing you can do (but it must be done beforehand) is to set your xla file to read-only. That way you can always replace it, without closing Excel. The new version will be loaded the next time the user starts Excel.
2. The Add-in IS the xla file, so I'm not sure what you're asking here.

Comment by: Gaetan (6-12-2010 09:42:24) deeplink to this comment

Hi Jan,

Thanks for your answer, the read-only trick is interesting !

2/ When you install an xla file, can you install it in any directory or do you need to choose the Excel add-in directory. The former seems to work for me, but I would like to have confirmation that I don't create a potential problem.

Regards

Gaetan

Comment by: Jan Karel Pieterse (6-12-2010 10:30:15) deeplink to this comment

Hi Gaetan,

Any directory is fine.

Comment by: ahn dae yeong (20-1-2011 04:41:43) deeplink to this comment

thanks

Comment by: Dutch (21-1-2011 07:55:59) deeplink to this comment

I have created a script for InnoSetup that installs the Add-Ins for me. This has been quite a job since InnoSetup uses Pascal and is very picky on errors.

This setup registers/deregisters it in Excel, takes care of upgrading but also cleaning up the system. Runs on Windows 2000 all the way up to 7 and makes an entry in the control Panel.

It is also possible to perform a Current User/All Users installation and it automatically links to the correct version of Excel (2000 op to 2010).

Comment by: Jan Karel Pieterse (22-1-2011 08:40:52) deeplink to this comment

Hi Dutch,

Would you be willing to share that code with us?

Comment by: Dutch (24-1-2011 09:47:04) deeplink to this comment

I will post it on my blog soon (with a little adaptation) and forward you the link next.

Current size, with all the needs for my app (i.e. CHM, PDF's, samples folders etc.), is 1226 lines of code which I think may pose a little problem if I try to embed it in this page.

Dutch

Comment by: Syrma (21-4-2011 06:33:27) deeplink to this comment

Hi Dutch,

I'm also interested to get a way to load XLA for all users. Is your script doint that?

If yes, is that possible to share it?

Many thanks

Comment by: LAWindsurfer (16-8-2011 13:39:50) deeplink to this comment

Does using an AddIn add to the size on an Excel file. If one uses the features of a specific AddIn in a workbook, does that AddIn travel with the workbook and increase its file size? Alternatively, is it merely that if workbook is opened on machine without the AddIn, the features of that AddIn will not be available.

Comment by: Jan Karel Pieterse (22-8-2011 02:09:31) deeplink to this comment

Hi Windsurfer,

No, add-ins do not travel with a workbook. You're right that if the add-in isn't available, the functionality of the addin will also not be available.

Comment by: Stefano Gatto (6-12-2011 03:04:23) deeplink to this comment

Hello,

Many thanks for this comprehensive article. I have a question for which I did not see the explicit answer above, so maybe you can help me directly.

I'm writing an Addin that is meant to work on XL 2007, 2010 and beyond. I need to find out what is the folder where the addins are stored for the connected user. Where do I find that folder in the registry, regardless of the Excel version that is executing my addin?

From your message above this can be either:

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

or

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

depending from the version, but I would need one way of finding it, indirectly most probably.

In other words, how do I find programatically what is the folder that gets open when i "SAVE" a workbook "AS" an Excel Addin?

Thank you.
Stefano

Comment by: Jan Karel Pieterse (6-12-2011 08:32:54) deeplink to this comment

Hi Stefano,

You are mixing two entirely different things.

The HKEY_CURRENT_USER path is the location *in the Windows Registry* where Excel stores information on its settings, including add-ins.

The other item is the folder where Excel stores its Add-ins depends on your operating system and your Office installation.

Mostly it is here:

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

But sometimes here:

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

(Exact path depends on installation and version number)

Comment by: Stefano Gatto (7-12-2011 15:57:54) deeplink to this comment

My question was confusing... Let me formulate it again.

Is there a way to determine the folder where Addins are saved by default, when the user SAVES a workbook AS an Excel addin?

I know that for winxp it's c:\program files\documents and settings\username\application data\microsoft\addins, but my question is how would a program know this without me hardcoding it? Is there a registry location holding this folder name? This would make my program more resistent to new versions of excel and windows.
thank you.

Comment by: Jan Karel Pieterse (8-12-2011 01:09:18) deeplink to this comment

Hi Stefano,

From VBA this is easy:

Application.UserLibraryPath

Or (where Excel installs its own addins):

Application.LibraryPath

Comment by: Dutch (24-1-2012 04:54:04) deeplink to this comment

As promised some time ago, I am sharing my article about installing an Add-In (XLA and XLAM) into Excel (all versions) with InnoSetup: http://wp.me/pe53Z-2G

It took much more than I wanted (because I have other work to do) but are satisfied with the result.

Enjoy.

Comment by: Jan Karel Pieterse (24-1-2012 06:00:25) deeplink to this comment

Hi Dutch,

Thanks!

Comment by: Johann Van Antwerpen (31-1-2012 02:07:34) deeplink to this comment

Very nice article, Thank You.

Question:
If MS Excel at time crash and an Add-in is disabled by the user, under which registry key is this stored?

If I can script a registry "fix" to delete the Disabled Add-in Key, users will receive the Add-in fully functional each time MS Excel 2010 is launched.

Would you be able to help with this or one of the members within this post?

Appreciate the help and guidance!

Thank You
Johann

Comment by: Jan Karel Pieterse (31-1-2012 06:04:28) deeplink to this comment

Hi Johann,

I'm afraid I don't know.
All I can say is look in the registry if you happen to have a disabled file, as I sure don't!

Comment by: Neil Rickards (23-3-2012 02:58:36) deeplink to this comment

Hi Johann,

For Office 2003 you can find a subkey called DisabledItems under HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Resiliency

Thanks,
Neil

Comment by: Fabio Brito (25-6-2012 19:57:16) deeplink to this comment

Hello there,

First of all, congratulations for your article.
I'm trying to deploy this setting to network computers, and my scenario is: the xla file is stored on a network drive (\\unc_path\x.xla), but even after set the registry values the addon doesn't show on default menu (Office 2003).
I guess there is another keys to be set, but I didn't find it... Any tip that what I'm missing?

Thanks!

Comment by: Jan Karel Pieterse (26-6-2012 06:47:10) deeplink to this comment

Hi Fabio,

Which key(s) did you set?

Comment by: Jay (27-6-2012 03:42:23) deeplink to this comment

Do you have c# version of this code? Because I am a newbie pretty much to VB :-)

Comment by: Jan Karel Pieterse (27-6-2012 07:14:01) deeplink to this comment

Hi Jay,

No, I'm sorry. I have never written any C# code.

Comment by: Fabio Brito (27-6-2012 16:31:20) deeplink to this comment

Comment by: Jan Karel Pieterse (6/26/2012 6:47:10 AM)

Hi Fabio,

Which key(s) did you set?
---------------------------
Hi Jan,

Thanks for your promply response :-)
I've set the keys mentioned on your article, and I can see my company's add-on at add-ins list. I would like to include the add-on at the menu bar, to make the user's life easy :-)
If I uncheck the add-on and check again, the add-on goes to menu bar, is there any way to do this automaticaly?

Thanks!

Comment by: Jan Karel Pieterse (27-6-2012 17:00:04) deeplink to this comment

Hi Fabio,

You Probably need to use a different event to call your menybar code (in de addin), like Workbook_Open instead of addinInstall.

Comment by: Siraj Zarook (25-9-2012 18:51:03) deeplink to this comment

Hi

I have complete different question, we use .net com interop dll add-in with Excel.

My issue is running two different version of dll as we have to release a new version of our product.

I have created a new DLL and given in different CLSID. As register the new dll my Excel product does seem to be working correctly and it gets confused with two different COM Add-In (.net dll).

If you have expertise in this area I would like to know if you have any idea about what is going on.

Kind Regards
Siraj Zarook.
s.zarook@biopharmservices.com

Comment by: Jan Karel Pieterse (26-9-2012 19:20:06) deeplink to this comment

Hi Siraj,

I'm sorry, that is outside my area of expertise.

Comment by: Bob (9-10-2012 05:11:59) deeplink to this comment

I have created an Excel COM Add-in - just finished converting a VBA to a VB.NET COM - and I want to use Setup Factory and your script. My questions are, after the COM is placed in the registry do I need to register the COM using "RegAsm.exe" or does the post install script take care of that?

Second, will the COM Add-In be visable in Excel the next time it is open?

Thanks,

Bob

Comment by: Jan Karel Pieterse (9-10-2012 10:22:07) deeplink to this comment

Hi Bob,

The code samples on this page are for *Excel* add-ins, not for COM add-ins. I have little to no experience installing .NET COM addins unfortunately.

Comment by: Bob (12-10-2012 21:03:26) deeplink to this comment

Jan,

You say you don't know about COM add-ins, but your scripting was very close to what I needed. I ended up buying Setup Factory and using your scripts as a basis for mine and was able to get them to work with a little different methodolgy, but your scripts put me on the right path! Thank you!

Bob

Comment by: Jerry Hoffman (24-10-2014 21:22:05) deeplink to this comment

When a file is opened on the computer it puts the path in front of the formula. How do we fix this? If you delete the path in front of the formula it works.
Any suggestions on how we have this happening that would be great.

Comment by: Jan Karel Pieterse (27-10-2014 09:45:26) deeplink to this comment

Hi Jerry,

Is that by any chance a formula using a User Defined Function? If so, check out this page:

https://jkp-ads.com/Articles/FixLinks2UDF.asp

Comment by: Astera (28-4-2015 14:56:27) deeplink to this comment

Hi, I have a question. In Excel 2007 we created AddIn in Workbook_AddinInstall. That worked fine and all Add-In Menu-Button were created.
After installing Excel 2013 no Add-In button are visible. Even if I put my Add-In installcode in ThisWorkbook Workbook_open(). Where do I have to put in my Add-In code to have all my Add-In Buttons in the Ribbon-Bar?
Thanks in advance
cheers Astera

Comment by: Jan Karel Pieterse (28-4-2015 15:42:28) deeplink to this comment

Hi Astera,

Excel 2013 is a bit buggy if you use the old menus and toolbars.

Sometimes it works to uncheck the add-in, close and open Excel and check the add-in again.

Comment by: Mike (30-3-2017 00:39:45) deeplink to this comment

None of these scripts will work if Excel is running. Installing an add-in will add it to Excel's "virtual" add-ins list but the registry is not updated until Excel quits. (I guess MS "engineers" are too lazy for real-time updating.) So if you install an add-in, then during that same Excel session want to change that add-in's path or remove the add-in you're doomed. There's nothing in the registry yet for updating, and even if you do programmatically update the registry it will do nothing for that "virtual" list of available add-ins populated when Excel was started. Thus, the original add-in stays in that damned list until Excel quits. Totally stupid implementation by MS for several versions of Office.

Comment by: Jhorbam Baena Orozco (13-10-2022 04:41:00) deeplink to this comment

Hello, I just read your article and tried to apply the code in my setup, but it gives me the following error:

Line=6:'then' expected near '&'

where line 6 is in my case:

if String.Find(sValue, "Microsoft Excel", 1, true)&gt;0 then

Can someone tell me what is wrong, thanks

Comment by: Jhorbam Baena Orozco (13-10-2022 05:24:00) deeplink to this comment

Hello everyone I have tried to use the code but trying to enter the startup code I get the following error: Line=6: 'then' expected near '&'
Try correcting the line:
if String.Find(sValue, "Microsoft Excel", 1, true)>0 then from this to this:
if String.Find(sValue, "Microsoft Excel", 1, true)then
     gt=0
else ..
Was the correction correct? If not, how should it be done?...thanks

Comment by: Jan Karel Pieterse (13-10-2022 11:50:00) deeplink to this comment

Hi,

I'm afraid I haven't done anything with SuF for ages, so I do not really know the answer. Perhaps you get more support here: https://forums.indigorose.com/forum

Comment by: Jarrod (20-6-2023 11:29:00) deeplink to this comment

Line=6:'then' expected near '&'

HTML-encoded character &gt;, which represents the greater-than symbol (>). To fix the error, you should replace &gt; with the actual greater-than symbol (>).


-- Next line has SetupFactory 8 code
-- for iCount, sValue in sValues do
for iCount, sValue in pairs(sValues) do
    if String.Find(sValue, "Microsoft Excel", 1, true) > 0 then
        Dialog.Message("Error", "First close Microsoft Excel."..String.Char(10)..String.Char(10).."Installer will now abort.", MB_OK, MB_ICONINFORMATION)
        Application.Exit(0)
    end
end

Comment by: Jan Karel Pieterse (20-6-2023 12:08:00) deeplink to this comment

Thanks Jarrod

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].