Thursday, June 23, 2011

Excel Add-In Script

This was originally created to register the iSeries Client Access Add-In with a workstation, although it could be used for other Add-Ins as well, just replace the "path" variable.

This assumes the Add-In is a 32-bit Add-In.  Not sure how this will behave with 64-bit Office, so please post your success or modifications.

To use, just save this as "Register Excel Add-In.vbs" and double-click.  To suppress dialogs, run from command line with "cscript.exe".


' Registers an Excel Plugin.
' Note: Run this script with cscript.exe to suppress dialogs
' Created 2/17/2011, Tres Finocchiaro
' Updated 6/21/2011, Tres Finocchiaro (64bit support, Reinstall support)
Option Explicit
' Uncomment this next line to suppress error messages
'On Error Resume Next
Dim xl, addin, shell, path, i, a, dll, is64,  path32, path64
' The path of the plugin.  This is safe to change
path = "%PROGRAMFILES%\IBM\Client Access\Shared\cwbtfxla.xll"
Set shell = CreateObject("WScript.Shell")
path32 = shell.ExpandEnvironmentStrings("%PROGRAMFILES%")
path64 = shell.ExpandEnvironmentStrings("%PROGRAMFILES(x86)%")
If (path64 <> "%PROGRAMFILES(x86)%" AND path64 <> "") Then
   path = Replace(path, "%PROGRAMFILES%", path64)
   path = Replace(path, "%PROGRAMFILES%", path32)
End If
' Only register plugin if the file exists
If (CreateObject("Scripting.FileSystemObject").FileExists(path)) Then
  Set xl = CreateObject("Excel.Application")
  xl.DisplayAlerts = False
  xl.Visible = False
  xl.UserControl = False

  ' Try to remove any old versions of the add-in
  a = Split(path,"\")
  dll = a(UBound(a))
  For i = 1 To xl.Addins.Count
     Set addin = xl.Addins.item(i)
     If addin.Name = dll Then
        addin.Installed = False
        WScript.Echo "Excel add-in successfully uninstalled:" & vbNewLine & vbTab & CHR(34) & path & CHR(34)
     End If

  ' Add the new version
  Set addin = xl.AddIns.Add(path, True)
  addin.Installed = True
  WScript.Echo "Excel add-in successfully installed:" & vbNewLine & vbTab & CHR(34) & path & CHR(34)
  Set xl = Nothing
  Set addin = Nothing
  Set shell = Nothing
End If

No comments: