Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > add-in Installation

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:

Indigo Rose Software
Setup factory company logo

Setup Factory allows you to rapidly create rock-solid, reliable software installers using a visual drag and drop environment. This compact, single-file setup.exe runs on any version of Windows from 95 to Vista.

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:


Tools, Add-ins list of Excel

You can navigate to this dialog as follows:

  • Excel 2003: Menubar, Tools, Add-ins
  • Excel 2007 and up: 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

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

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: 11.0 for Excel 2003, 12.0 for 2007, 14 for 2010, 15 for 2013.)
  • 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 (4/17/2007 3:02:56 PM)

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 (5/17/2007 2:34:31 AM)

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 (6/12/2007 3:06:40 AM)

Nice site!
a pleasant suprise

 


Comment by: Al Benton (7/27/2007 10:56:17 AM)

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 (7/27/2007 12:20:02 PM)

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 (8/1/2007 10:10:59 PM)

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 (8/24/2007 1:56:29 AM)

thanks for this great article :)

 


Comment by: hiutopor (9/17/2007 12:49:27 PM)

Hello

Very interesting information! Thanks!

G'night

 


Comment by: lokimikoj (9/22/2007 1:19:53 AM)

Hi

Very good site! I like it! Thanks!

 


Comment by: bill (10/12/2007 3:54:22 AM)

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

 


Comment by: Jan Karel Pieterse (10/12/2007 7:24:58 AM)

Hi Bill,

Could you tell me the error message?

 


Comment by: Jeroen Vreman (10/15/2007 1:00:56 AM)

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 (10/15/2007 1:03:08 AM)

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 (10/15/2007 1:04:25 AM)

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 (10/15/2007 7:19:05 AM)

Hi Jeroen,

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

 


Comment by: JamesCooks (11/24/2007 10:35:44 AM)

Nice.
Keep up the great work.

 


Comment by: Jeroen Vreman (12/7/2007 4:41:44 AM)

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 (2/6/2008 4:05:16 PM)

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

 


Comment by: swapnil (3/26/2008 2:02:25 PM)

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 (3/27/2008 8:17:49 AM)

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 (10/22/2008 12:40:58 AM)

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 (11/12/2008 1:05:14 AM)

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 (11/12/2008 1:37:04 AM)

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 (12/22/2009 9:10:52 AM)

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 (12/22/2009 10:52:02 AM)

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 (12/23/2009 2:05:00 AM)

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 (12/23/2009 4:15:00 AM)

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 (1/21/2010 8:32:46 AM)

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 (1/21/2010 8:50:28 AM)

Hi Steine,

Have a look at this page:

http://www.jkp-ads.com/Articles/FixLinks2UDF.asp

 


Comment by: Stein Magne Nilsen (2/2/2010 10:45:09 PM)

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 (2/3/2010 12:12:23 AM)

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 (2/3/2010 5:11:08 AM)

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 (2/3/2010 6:24:21 AM)

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 (2/3/2010 9:19:32 AM)

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

 


Comment by: Dutch (3/22/2010 8:28:27 AM)

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 (3/22/2010 8:35:45 AM)

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 9:10:26 AM)

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 (7/8/2010 11:22:31 AM)

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 (7/16/2010 1:17:09 AM)

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 (7/16/2010 6:30:26 AM)

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 (11/29/2010 7:22:42 PM)

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 (11/29/2010 11:20:42 PM)

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 (12/4/2010 7:11:40 PM)

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 (12/6/2010 12:49:13 AM)

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 (12/6/2010 9:42:24 AM)

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 (12/6/2010 10:30:15 AM)

Hi Gaetan,

Any directory is fine.

 


Comment by: ahn dae yeong (1/20/2011 4:41:43 AM)

thanks

 


Comment by: Dutch (1/21/2011 7:55:59 AM)

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 (1/22/2011 8:40:52 AM)

Hi Dutch,

Would you be willing to share that code with us?

 


Comment by: Dutch (1/24/2011 9:47:04 AM)

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 (4/21/2011 6:33:27 AM)

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 (8/16/2011 1:39:50 PM)

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 (8/22/2011 2:09:31 AM)

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 (12/6/2011 3:04:23 AM)

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 (12/6/2011 8:32:54 AM)

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 (12/7/2011 3:57:54 PM)

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 (12/8/2011 1:09:18 AM)

Hi Stefano,

From VBA this is easy:

Application.UserLibraryPath

Or (where Excel installs its own addins):

Application.LibraryPath

 


Comment by: Dutch (1/24/2012 4:54:04 AM)

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 (1/24/2012 6:00:25 AM)

Hi Dutch,

Thanks!

 


Comment by: Johann Van Antwerpen (1/31/2012 2:07:34 AM)

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 (1/31/2012 6:04:28 AM)

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 (3/23/2012 2:58:36 AM)

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 (6/25/2012 7:57:16 PM)

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 (6/26/2012 6:47:10 AM)

Hi Fabio,

Which key(s) did you set?

 


Comment by: Jay (6/27/2012 3:42:23 AM)

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

 


Comment by: Jan Karel Pieterse (6/27/2012 7:14:01 AM)

Hi Jay,

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

 


Comment by: Fabio Brito (6/27/2012 4:31:20 PM)

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 (6/27/2012 5:00:04 PM)

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 (9/25/2012 6:51:03 PM)

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 (9/26/2012 7:20:06 PM)

Hi Siraj,

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

 


Comment by: Bob (10/9/2012 5:11:59 AM)

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 (10/9/2012 10:22:07 AM)

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 (10/12/2012 9:03:26 PM)

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 (10/24/2014 9:22:05 PM)

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 (10/27/2014 9:45:26 AM)

Hi Jerry,

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

http://www.jkp-ads.com/Articles/FixLinks2UDF.asp

 


Comment by: Astera (4/28/2015 2:56:27 PM)

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 (4/28/2015 3:42:28 PM)

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 (3/30/2017 12:39:45 AM)

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.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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