Add-in Disabled in Excel

[thomasm516]

We are running Office 365 Pro Plus and the SAS Add-in 7.1 for Microsoft Office (32-bit).  We have had a few reports from users where the SAS tab does not appear in Excel.  It shows up in other Office applications, but not Excel.  If we go into the disabled add-ins and try to re-enable the add-in, it just will not re-enable.

Re-installing the SAS add-in does not fix the problem.  Reinstalling Office will bring the SAS tab back in Excel, but it ends up getting disabled again, usually very quickly, and we are back into the same boat.  At this point, I am not sure if this is a SAS issue or an Office issue, so I am pursuing both avenues.

Does anyone know how to resolve this issue without needing to reinstall Office endlessly?

Thanks for any help that you can offer!

--Tom

[carrieforeman]

Hi Tom,

Have you checked to see if the COM add-in is disabled as this can cause ussues with the Excel Add-in not displaying the menu - See SAS Note:

http://support.sas.com/kb/16/844.html

Hope this helps,

Carrie

[thomasm516]

Thanks for the reply.

The link that you posted seems to be an old article as my version of Excel does not have a Tools menu.  However, if I go to File > Options > Add-ins, I can see that there are no disabled application add-ins.  If I drop down the Manage box and go to COM Add-ins, the SAS Add-in 7.1 for Microsoft Office checkbox is cleared.  Unfortunately, if I check that box and click the OK button, it does nothing.  The add-in will not re-enable.  This is what I meant in my original post.  Sorry if that was not clear.

Thus far, the only way I have been able to re-enable the add-in is to do a full reinstall of Office.  That was okay the first time, since I thought it might just be a one-off situation.  But now that the problem has recurred for the same user on the same machine, obviously doing repeated reinstalls of Office is not going to cut it.

--Tom

[thomasm516]

I have a theory based on my reading of other posts with the same issue.  I think the reason that I cannot get the SAS add-in to load is that it was disabled.  Initially, I did not realize the difference between disabled add-ins and those that just are not loaded.  So I was going to the COM add-ins to try to load the SAS add-in, and I suspect it was not loading because it was disabled.

Unfortunately, before coming to that realization, I managed to make things worse.  Now the SAS add-in does not appear at all in the list of COM add-ins.  I think that I will reinstall SAS in order to get it back, and then if the issue persists I will verify whether or not the add-in is disabled before I try to load it.

I will post an update once I have more information.

--Tom

[CaseySmith]

Hi Tom,

I highly recommend trying the SwitcherUtility.exe tool (in your AMO installation directory; ex. C:\Program Files (x86)\SASHome\x86\SASAddinforMicrosoftOffice\7.1) to get the SAS Add-in for Microsoft Office loading again.  (I'd certainly try that before re-installing.)  It is typically used to switch between versions of the add-in (when you have more than one version installed), however it re-registers the add-in, which usually fixes up problems with it not appearing.

Here is a screenshot of the utility:

Note: Make sure you run it as an administrator, since it needs permission to update the Windows registry for it to be able to register the add-in.

If that doesn't do the trick, @TimBeese should be able to get you going again.

Casey

[thomasm516]

Casey,

Thanks.  I came across the Switcher utility while doing some reading this morning in the support forums.  I did give it a try, but it did not make the add-in reappear.  That said, I did not know about it re-registering the add-in, so that is helpful information.  I have added the Switcher utility to my ever growing SAS notes file for help with future troubleshooting.

And thanks for the link to Tim Beese.  I will add that page to my list of resources.

--Tom

[TimBeese · Accepted solution]

Tom,

I thought I'd send you a checklist of some things that I've tried before that have helped get the add-in loaded.  Some you may have already tried, some may be things that you can try.  Hopefully one of them gets it working for you.

A. Checking for disabled items in Excel

     1. Open Excel, go to File->Options

     2. On the Add-in page, there are 3 sections: Active, Inactie, and Disabled.  Check which list the SAS Add-in appears in.

     3. If it appears in the Disabled list, go to the "Manage" combo box at the bottom and choose Disabled Items, then click Go...  Click on the SAS Add-In in the list of items to re-enable, and then Close.

     4. Next, click on COM add-ins in that same combo box, and click Go...  Check the checkbox next to the SAS Add-in and click OK.  This should cause the add-in to load.

     5. If it does NOT load, go back to File->Options->add-ins->COM addins->Go...  In this dialog if you select the SAS Add-in, there will be a load behavior at the bottom.  If there was an error during loading, it will put it here.  This will let us know if Excel is at least trying.

B. Checking the registry directly

     1. Run regedit (as the user who invokes office)

     2. Navigate to the following key:  HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\SAS.ExcelAddin

     3. See what the LoadBehavior is set to.  You want it set to 3.

C. Reset your add-in registry settings with SwitcherUtility

     1. From a command prompt, navigate to the directory where you have the Add-In installed.

     2. Run:   SwitcherUtility.exe ?

     3. This shows a list of all the command line arguments.  This has some extra options that are not available in the UI.

     4. Run:   SwitcherUtility reset            // This will reset information in the current user portion of the registry.  You want to do this as the user who is running Office, so that user's registry settings are reset, not the administrators.

     5. See if this resolves the issue

     6. Run:  SwitcherUtility resetall         // This will reset information in the local machine portion of the registry as well.  You must be running as an administrator to do this.

     7.  See if this resolves the issue

     8.  Run:  SwitcherUtility register     // This will re-register the add-in assemblies

D. Are you in a virtualized environment?  I've seen cases where each time a user logs in, their add-in gets disabled.  This was because their registry was getting "reset" each time they logged in, and the values that were being reset had the add-in disabled.  It doesn't sound like this is your case, but if it is, it's something to talk about with your system administrator.

E. Run the VSTO file

     1. Go to the folder where you installed the SAS Add-in.

     2. Find SAS.ExcelAddin.vsto

     3. Double-click this file

     4. This is essentially using the vsto file to register the add-in instead of our own process.  I don't usually suggest this, but when nothing else seems to be working to get the add-in enabled, this will often give a more helpful reason for the failure.

Hopefully one of these strategies will do the trick.  Feel free to contact me if you continue to have issues getting the add-in to load and I can take a closer look.

Tim Beese

[thomasm516]

Tim,

Thanks for the information.  I had tried A and C, but not B and E.  D does not apply in this case, but the information is good to have anyway.  I have added all that information to my notes.

I think that my previous troubleshooting efforts were on the wrong track, and that I ended up making the issue worse, so I elected the remove and reinstall all the SAS components, just to get back to a clean slate.  The SAS tab is now back and everything seems to be running fine.  If the problem returns, like it did before, I will have more of an idea of what to look at, and will definitely try all the things that you mention before doing anything else.

For now, we can consider this one closed.  I'd like to thank everyone who contributed information to this discussion.  I have learned a lot!

--Tom

[kwyland1]

What if it does not appear at all?

A. Checking for disabled items in Excel

     1. Open Excel, go to File->Options

     2. On the Add-in page, there are 3 sections: Active, Inactie, and Disabled.  Check which list the SAS Add-in appears in.

We use SAS EG or SAS Studio to connect to a Enterprise Server so have no base sas on local Windows PC.  Before we got Office 365, it was an available option to add in that com and could see the SAS menu for the add-in.  
What I'm trying to figure out is how to help users and administrators to know what the next steps are!  

[TimBeese]

If the SAS Add-In for Microsoft Office does not appear at all in that list, then it is not installed (or registered) properly.  I would suggest the following two options.

1. Run the SwitcherUtility.exe from the add-in's installation directory.  This will set up the registry so that Excel can find the add-in.  If you look at item "C" from the accepted solution on this discussion, it lists some arguments that you can pass in that will help make sure that the registry gets updated properly.

2. Re-install the add-in.  Perhaps some files are missing or the registry just needs to be wiped clean and then re-registered when you do the install.  Hopefully this isn't needed, step #1 should be able to fix most registry issues.

Other things you may need to consider is if you installed the proper bitness of the add-in.  It must match the bitness of Office, not the bitness of the OS.  Also, you have to be running the desktop version of Excel, not the web version.  The web version doesn't support COM add-ins.

Hope this helps,

Tim Beese