Scripting

Here is a VBScript Custom Action. This works well for us, you may have to modify as see fit within your organization.

______START______DO NOT COPY THIS LINE.

Dim strPath,wshShell

Set WshShell = CreateObject("WScript.Shell")

strPath=("myaddin.myAddinName")


strPath= "/A """ & strpath & """"


DelXLL2 2003,strPath
DelXLL2 2002,strPath
DelXLL2 2000,strPath


Function DelXLL2( iExcelVersion, sAddin )
' 1.0
' Pull in and write out XL addin list, excluding "sAddin" if found.


DelXLAddin = False

Set oWshShell = CreateObject("WScript.Shell")

Select Case iExcelVersion

Case 97
sRegKey = "HKCU\Software\Microsoft\Office\8.0\Excel\Microsoft Excel\OPEN"
sAddin = sAddin

Case 2000
sRegKey = "HKCU\Software\Microsoft\Office\9.0\Excel\Options\OPEN"
sAddin = sAddin
Case 2002
sRegKey = "HKCU\Software\Microsoft\Office\10.0\Excel\Options\OPEN"
sAddin = sAddin

Case 2003
sRegKey = "HKCU\Software\Microsoft\Office\11.0\Excel\Options\OPEN"
sAddin = sAddin

Case Else
' nothing
End Select


ReDim aAddin(0)
aAddin(0) = ""
iIndex = 0

Do
'pull all existing addins into array:
' - OPEN
' - OPEN1
' - OPEN2
' - ... until no more entries

On Error Resume Next

If iIndex=0 Then
aAddIn(0) = oWshShell.RegRead(sRegKey)
oWshShell.RegDelete sRegKey
iIndex = 1
ReDim Preserve aAddin(1)
Else
aAddIn(iIndex) = oWshShell.RegRead(sRegKey & iIndex)
oWshShell.RegDelete sRegKey & iIndex
iIndex = iIndex + 1
ReDim Preserve aAddin(iIndex)
End If

If Err.Number <> 0 Then
Err.Clear
On Error Goto 0
Exit Do
End If

Loop Until False

' write back array to registry

iNewIndex = 0

If iIndex = 0 Then
' no addins
Else
For i=0 To iIndex-2 'will always have gone 1 over limit of array, which is zero-bounded
If UCase(aAddin(i)) <> UCase(sAddin) Then
If iNewIndex=0 Then
oWshShell.RegWrite sRegKey, aAddin(i), "REG_SZ"
iNewIndex=1
Else
oWshShell.RegWrite sRegKey & iNewIndex, aAddin(i), "REG_SZ"
iNewIndex = iNewIndex + 1
End If
Else
DelXLAddin = True 'return indication that XL addin was deleted
End If
Next
End If

End Function

______END______DO NOT COPY THIS LINE.


-Good luck

Hey,

Thanks a lot. Its working.

Glad I could help. This is a great site.

Hey,

I have one query related to Excel Addins.
My developer has given me an .xla file which is a addin file to be added in his excel.
This addin needs to be added through vb script. So that automation can be achived in adding or removing addins.
I am using the below mentioned script to remove the addin and it works perfectly but for one of addin file this script is throwing me a exception error.

oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Set oAddin = oXL.AddIns.Add(filename, True)
oAddin.Installed = False
oXL.Quit
Set oXL = Nothing


At the line no. 4 addin is failing to remove and throwing me exception error.
Can you help?

Regards,
Neel

you are attempting to add or remove??

I presume one of the issue you're running into is with the OPEN order..

OPEN
OPEN1
OPEN2
OPEN3
etc...

If your script installs to the first available key, that's fine (as long as it already doesn't exist because then you're just overwriting a pre-existing value).

Now lets say your script places the next value as OPEN4. You may have overwritten that with another addin. Your script is most likely looking for that addin name but cannot find it because it was replaced with something different.

That's why you have to create the key in the next available position.

The remove script that I posted above, search through the open keys until it finds the correct one, then it re-orders the OPEN orders so there are no blank spaces.

Example... If you have:

OPEN
OPEN1
OPEN3

but not OPEN2, that will be a problem. Not only that, but any addin after the blank OPEN will not open.

Of course, I may just be barking up the wrong tree with my comments above because you're not really very clear with your intentions. Please be more specific.

Good information, Just curious how would I modify the above script if I wanted to ADD the excel Analysis Toolpak in Office 2003 Pro?

Neel,
The line:
Set oAddin = oXL.AddIns.Add(filename, True)

Should be "Set oAddin = oXL.AddIns.Add(filename, False)" for removal.

CGTECH,

Here is the code for adding and removing the Toolpack.

Dim strXLApath : strXLApath = "C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis\analys32.xll"

'Call RemoveXLA(strXLApath)

'Call AddXLA(strXLApath)

Sub AddXLA(strXLApath)
Dim oXL : Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Dim oAddin : Set oAddin = oXL.AddIns.Add(strXLApath, True)
oAddin.Installed = True
oXL.Quit
Set oAddin = Nothing
Set oXL = Nothing
End Sub




Sub RemoveXLA(strXLApath)
Dim oXL : Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Dim oAddin : Set oAddin = oXL.AddIns.Add(strXLApath, False)
oAddin.Installed = False
oXL.Quit
Set oAddin = Nothing
Set oXL = Nothing
End Sub


Regards to All,

Robo Scripter,
I've used your script and it works great. I'm working on a package which has 17 addins, how to I modify your script to include multiple addins.

- create a list of the add-ins in an array
- loop through the array, parsing the name of the add-in
- call the AddXLA routine, passing the name to it.

Note that, like most QAD (quick and dirty) scripts, there is NO error-trapping in the example above, which you would be well advised to add.