Programming Excel with VBA and .NET

Distribute the Add-in

The easiest way to distribute an add-in is to copy it to the user’s AddIns folder . That is the default location to which Excel saves add-ins, and any add-ins placed there are automatically displayed in the Add-Ins dialog box (Figure 22-3).

The following VBScript file installs an add-in in the user’s AddIns folder and loads the add-in in Excel:

    ' InstallAddin.vbs
    ' Get the objects used by this script.
    Dim oXL, oAddin, fso, wsh, srcPath, destPath, addin
    addin = "\ch22.xla"
    Set oXL = CreateObject("Excel.Application")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = WScript.CreateObject("WScript.Shell")
    ' Make Excel visible in case something goes wrong.
    oXL.Visible = True
    ' Create a temporary workbook (required to access add-ins)
    oXL.Workbooks.Add
    ' Get the current folder.
    srcpath = fso.GetFolder(".")
    destPath = wsh.Environment("PROCESS")("HOMEDRIVE") & _
      wsh.Environment("PROCESS")("HOMEPATH") & _
      "\Application Data\Microsoft\Addins"
    ' Copy the file to the template folder.
    fso.CopyFile srcpath & addin, destpath & addin
    ' Add the add-in to Excel.
    Set oAddin = oXL.AddIns.Add(destpath & addin, true)
    ' Mark the add-in as installed so Excel loads it.
    oAddin.Installed = True
    ' Close Excel.
    oXL.Quit
    Set oXL = Nothing

To use the preceding VBScript installer with your own add-ins:

1. Change the addin variable to match your add-in filename.

2. Place the add-in and setup file in the same folder. That can be a public folder on your network, a folder on a CD, or some ...