Solved

disable excel add-in

Posted on 2016-10-25
3
21 Views
Last Modified: 2016-11-15
hi all,

I'm looking for information on how to script the removal of some excel add-in.

what I tried so far is some PowerShell that creates a COM object with excel.application, locates the add-in to remove and set it to not installed with:

($wb.Application.AddIns | Where-Object { $_.name -eq 'sapbex.xla' }).Installed = $false

but when I open excel, the add-in is still enabled.

is there someone here with more information on this, or that has a clue about what I am doing wrong?

thank you!
0
Comment
Question by:Daniel Brassard
  • 3
3 Comments
 
LVL 17

Accepted Solution

by:
xtermie earned 500 total points (awarded by participants)
Comment Utility
The standard way for an Excel addin is to set the Installed property to False (or True) to disabled (or enable) the addin.

 Sub Toggle_Analysis_AddIn()
   Dim A As AddIn
   For Each A In Application.AddIns
     If A.Name = "ANALYS32.XLL" Then
       A.Installed = Not A.Installed
       Exit Sub
     End If
   Next
 End Sub

Open in new window


 The same can be used for COMAddIns, if you have any.

 If Application.COMAddIns(1).Connect Then
    MsgBox "The add-in is connected."
Else
    MsgBox "The add-in is not connected."
End If

Open in new window

There is a possibility that explains the behavior that you describe. If you disable the addin (also manually) in Excel and the addin is back if you reopen Excel, in fact means that something re-enables the addin. But that is not Excel.  The most reason for that are third party tools, which installs a service in windows (this is a running DLL in memory, not listed in the task manager). And that services forces the state of the addin.

Whenever you install an add-in in Excel, it is added to an internal list of available add-ins. This list is used by Excel whenever you display the Add-ins dialog box. (The Add-ins dialog box is where you are shown available add-ins and can use a check box to indicate which should be enabled when running Excel.) Removing a previously installed add-in so that it doesn't appear in the Add-ins dialog box can be a confusing process, since Excel provides no way to directly edit that internal list of add-ins. You can get around this frustration, however, by following these steps:

1. Get out of Excel.
2. Use the tools in Windows (such as the Search tool) to locate the add-in file you want removed. You can locate add-in files by their file name extension: either .xla or .xlam, depending on your version of Excel.
3. Delete, rename, or move the add-in file.
4. Start Excel. If the add-in was one that was automatically loaded when Excel first starts, you will get a message from Excel indicating that it cannot find your missing add-in file.
5. Answer Yes when prompted if you want to remove the add-in file from the list.
6. If the add-in was not one that started automatically, choose Add-Ins from the Tools menu. Excel displays the Add-Ins dialog box, and your deleted add-in should still be listed as available. (Remember; this dialog box represents the internal list of add-ins, and hasn't been updated.)
7. Select the check box next to the add-in you want deleted. This informs Excel you want to use the add-in.
8. Click on OK. You should get the same message described in step 4.
9. Answer Yes when prompted if you want to remove the add-in file from the list.
0
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 500 total points (awarded by participants)
Comment Utility
If none of the above works, it is possible to create a VBscript that deletes the registry key. Start the VBS shortly before you close Excel, the VBS itself has to use a delay (to be sure Excel is closed), then it can delete the key and reopen Excel.

 
Option Explicit

 Const TestAddIn = "C:\atest.xla"  'change with your add in

 Sub CreateAddIn()
   Dim W As Workbook
   Set W = Workbooks.Add
   W.IsAddin = True
   W.SaveAs TestAddIn, xlAddIn
   W.Close
 End Sub

 Sub LoadAddIn()
   AddIns.Add TestAddIn, False
   AddIns("atest").Installed = True
 End Sub

 Sub RemoveAddIn()
   Dim fso As FileSystemObject
   Dim ts As TextStream
   Dim Script As String, ScriptFile As String
   Dim A As AddIn
   Dim objShell
   
   'Access the addin to remove
   Set A = AddIns("atest")
   A.Installed = False

 Script = _
 "On Error Resume Next" & vbCrLf & _
 "'Wait until Excel is closed" & vbCrLf & _
 "WScript.Sleep 1000" & vbCrLf & _
 "'Here is the place where Excel stores the AddIns list" & vbCrLf & _
 "RegPath = ""Software\Microsoft\Office\[Version]\Excel\Add-in Manager""" & vbCrLf & _
 "'Delete the addin from the list" & vbCrLf & _
 "Set oReg = GetObject(""winmgmts:{impersonationLevel=impersonate}!"" & _" & vbCrLf & _
 """\\.\root\default:StdRegProv"")" & vbCrLf & _
 "oReg.DeleteValue &H80000001, RegPath, ""[AddIn]""" & vbCrLf & _
 "'Restart Excel" & vbCrLf & _
 "Set objShell = CreateObject(""Wscript.Shell"")" & vbCrLf & _
 "objShell.Run ""excel.exe""" & vbCrLf & _
 "'Delete this script" & vbCrLf & _
 "Set fso = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf & _
 "fso.DeleteFile Wscript.ScriptFullName"

   Script = Replace(Script, "[Version]", Application.Version)
   Script = Replace(Script, "[AddIn]", A.FullName)

   'Create the VBScript
   Set fso = CreateObject("Scripting.FileSystemObject")
   ScriptFile = Replace(fso.GetTempName, ".tmp", ".vbs")
   ScriptFile = fso.BuildPath(Environ$("temp"), ScriptFile)
   Set ts = fso.CreateTextFile(ScriptFile)
   ts.Write Script
   ts.Close
   
   'Run it and close Excel
   Set objShell = CreateObject("Wscript.Shell")
   objShell.Run ScriptFile
   Application.DisplayAlerts = False
   Application.Quit
 End Sub

Open in new window

0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
valid recommendations provided by experts
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This article will help you understand what HashTables are and how to use them in PowerShell.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now