VSTO & .NET & Excel

October 17, 2009

Install and Activate Add-ins in Excel with SamLogic’s Visual Installer

Filed under: COM Add-ins, Installation Tools, Tools, VSTO & Excel — Dennis M Wallentin @ 1:10 am

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:

Visual Installer 2008

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):

Windows Registry Entry

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

About these ads

10 Comments »

  1. Very cool Dennis,

    The Visual Installer 2008 (and 2010) look pretty cool. Anything that makes Registry as simple as a single line of code can’t be a bad thing.

    Do you know if this tool be used as part of a Setup Package or be used to generate a .REG file so that we could include it as part of a Setup Package?

    Anyway, neat article Dennis, as always, I’ll have to check this out…

    – Mike

    Comment by Mike Rosenblum — October 19, 2009 @ 8:06 pm

    • Mike,

      As long as we use Visual Installer it can be part of a Setup Package. We can create setups that include all kind of solutions where this can be part of it. But all the configuration is done silently so there exist not any Reg file.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — October 20, 2009 @ 1:32 am

  2. Cool, very good to know Dennis. :-)

    I will definitely have to check this out at some point…

    Mike

    Comment by Mike Rosenblum — October 20, 2009 @ 3:37 am

  3. AFAICS Visual Installer is on sale for £1,700 for a developer license plus more costs per seat.
    Rather too pricey IMO.

    Comment by Patrick O'Beirne — October 20, 2009 @ 1:32 pm

    • Patrick,

      I don’t know from which source You got that price information from but SamLogic’s Visual Installer 2008 Professional version costs SEK 2490 (+ vat. )

      With today’s exchange rate that equals:
      US $ 358,40
      £ 220,65
      Euro 240,28

      You must have mixed it with some other software.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — October 20, 2009 @ 3:41 pm

  4. Sorry, Dennis, I see it now.
    I was using Google Translate to read the Swedish and it gave me
    “SamLogic Visual Installer 2008 (upgrade): * £ 1490 ”

    Obviously arbitrarily sticking in a pound sign as its idea of currency conversion!

    If I had stopped to check my own currency converter
    http://www.sysmod.com/eurocalc/eurocalc.php
    I’d have seen the 10:1 ratio.

    Apologies to Sam!

    Patrick

    Comment by Patrick O'Beirne — October 20, 2009 @ 5:06 pm

  5. Patrick,

    I suspected it was something like that. Google Translate is excellent in many ways but it does not yet convert currency correctly.

    Glad we got it sorted out :)

    All the best,
    Dennis

    Comment by Dennis Wallentin — October 20, 2009 @ 5:19 pm

  6. So when can we expect them to offer an English version of the installer?

    Comment by Jan Karel Pieterse — October 23, 2009 @ 4:22 pm

    • J K,

      Yes, I have this week agreed with SamLogic to be part of the work to translate it to an English version. The same set up have also been arranged for their license tool RegGuard.

      The follow up question is; when will it be available? Hopefully next year :)

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — October 23, 2009 @ 4:27 pm

  7. A new improved version, that supports Windows 8, is now available. You can read more at: http://www.samlogic.net/visual-installer/visual-installer.htm

    Comment by Anders Persson — January 16, 2013 @ 8:31 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 39 other followers

%d bloggers like this: