In the first part of this post I discuss how to work with the collection of add-ins as well as individual add-ins in Excel when using VB.NET/VSTO. In the second part the installation tool Visual Installer will be discussed in terms of installing and activating native add-ins in Excel.
Part I – Add-ins
When working with add-ins we need to separate native add-ins and unmanaged/managed COM and VSTO add-ins from each other. In this example I use a VSTO add-in to call the collections and add-ins.
If we want to load, i.e. activate, an add-in during a session the code below shows how to do it:
Private Const m_sXLDATA As String = "Data.xlsm"
Private Const m_sXLAREPORT As String = "C:\Data\Report.xlam"
Private Const m_sXLAREPORT_DISPLAYNAME As String = "Report"
Private Sub Application_WorkbookOpen( _
ByVal Wb As Microsoft.Office.Interop.Excel.Workbook) _
Handles Application.WorkbookOpen
Try
If Wb.Name.ToString() = m_sXLDATA Then
With Globals.ThisAddIn.Application
.AddIns.Add(Filename:=m_sXLAREPORT)
.AddIns(m_sXLAREPORT_DISPLAYNAME).Installed = True
End With
End If
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub
Private Sub Application_WorkbookBeforeClose( _
ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, _
ByRef Cancel As Boolean) _
Handles Application.WorkbookBeforeClose
Dim xlAddin As Excel.AddIn = Nothing
Try
If Wb.Name.ToString = m_sXLDATA Then
For Each xlAddin In _
Globals.ThisAddIn.Application.AddIns
If xlAddin.FullName = m_sXLAREPORT Then
xlAddin.Installed = False
Exit For
End If
Next
End If
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub
As we can see it does not differ from how we do it in VBA. If we want to load a COM add-in or a VSTO add-in it can be done in the following way:
Private m_xlCOMAddins As Office.COMAddIns
Private m_xlCOMData As Office.COMAddIn
Private Const m_sXLDATA As String = "Data.xlsm"
Private Const m_sXLDATAANALYZE As String = "SQL Tester NET 2010.AddinModule"
Private Sub Application_WorkbookOpen( _
ByVal Wb As Microsoft.Office.Interop.Excel.Workbook) _
Handles Application.WorkbookOpen
m_xlCOMAddins = Globals.ThisAddIn.Application.COMAddIns
m_xlCOMData = m_xlCOMAddins.Item(m_sXLDATAANALYZE)
Try
If Wb.Name.ToString() = m_sXLDATA Then
If m_xlCOMData.Connect = False Then _
m_xlCOMData.Connect = True
End If
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub
Private Sub Application_WorkbookBeforeClose( _
ByVal Wb As Microsoft.Office.Interop.Excel.Workbook, _
ByRef Cancel As Boolean) _
Handles Application.WorkbookBeforeClose
m_xlCOMAddins = Globals.ThisAddIn.Application.COMAddIns
m_xlCOMData = m_xlCOMAddins.Item(m_sXLDATAANALYZE)
Try
If Wb.Name.ToString = m_sXLDATA Then
If m_xlCOMData.Connect Then _
m_xlCOMData.Connect = False
End If
Catch ex As Exception
MsgBox(ex.Message.ToString())
End Try
End Sub
Again, it does not differ from how it’s done in VBA.
Part 2 SamLogic’s Visual Installer
In the past I have made comments on various blogs and at Q&A forums about an installation tool that can help us with the installation and with the activation of native Excel add-ins in a smooth way. I finally decided to make a blogpost about this superb tool. I have been using it for years and so far I have not seen any other tool that makes it so easy to set up the installation instructions. The software I talk about is Visual Installer 2008 from the Swedish company SamLogic.
At present Visual Installer 2008 is available in Swedish but it can create set up packages in English. Anyway, whenever I need to distribute native add-ins and related files I use it. It also works great with .NET packages including managed COM add-ins.
For the demonstration purpose here I use an add-in with name Report.xlam. To set it up in Visual Installer it only requires one line (!) as the below screen shot show:
The command XLADDIN trigger Visual Installer to add the required entry in the Windows Registry. After the installation the entry exist in the Registry as the following screen shot shows (OPEN5):
When we uninstall the add-in the entry in the Windows Registry is silently removed. All in all, I find it to be an excellent companion.
Although I do not agree I know some VBA developers that have several Excel versions installed side by side. When using Visual Installer the add-ins are installed once but are registered for all available Excel versions on configurations with multiply versions installed.
The next version, i.e. Visual Installer 2010, will also support the 64-bits Windows platform as well as Windows 7.
Kind regards,
Dennis
VSTO & .NET & Excel 
