1

I am having difficulty with the load behavior of an Excel Ribbon Add-In developed as a VSTO solution. The Add-In serves as an Excel VSTO Ribbon control that has several tabs, each tab's visibility is set to false by default and made visible by the workbook's Add-In instantiation.

For reasons that are unclear to me allowing the ClickOnce install to set the LoadBehavior to 16 (then a subsequent 9) is problematic. The initial value of 16 will load the workbook ribbon tab properly but after closing and reopening the same workbook the ribbon tab is not displayed.

However if another workbook is opened with the same Add-In, both tabs appear in each of the workbooks.

The LoadBehavior of "0" works as intended in any case and is the reason for the posted question.

Here is the VBA code for the Add-In instantiation

'This loads the Ribbon Addin
Private Function LoadAddIn() As Boolean
    On Error GoTo Err_LoadAddIn
    Dim msg As String
    Dim m_addIn As COMAddIn
    Dim m_automationObject As Object
    Dim m_sWorkbookKey As String    

    msg = "Unable to load the PITA AddIn, please contact PITA support"

    'Load the Excel Addin
    Set m_addIn = Application.COMAddIns("PITA Ribbon")
    'Connect the COM Add-In to the current workbook
    m_addIn.Connect = True

    'Set a reference to the utility class that the COM Add-In references
    m_automationObject = m_addIn.Object
    'If it is nothing then the Add-In is in a bad state
    If m_automationObject Is Nothing Then
        msg = "Error loading the PITA AddIn, please contact PITA support"
        GoTo Err_LoadAddIn
    Else
        'Set the service type of the Add-In (currently only SQLServer)
        m_automationObject.SetDataConnection "SQLServer"
        'Set the workbook key - this is the name of the analyzer
        m_sWorkbookKey = Worksheets("SheetX").Range("A10")

        'Set the ribbon tab's visibility relative to the current workbook
        m_automationObject.SetTabVisibility m_sWorkbookKey, True

        'If the connection to the datasource is successful
        'Populate the Tab's List Controls with the values from SQL Server
        If m_automationObject.Connected = True Then
            m_automationObject.SetTabDefaults m_sWorkbookKey
        End If
    End If

    LoadAddIn = True
    Exit Function

Err_LoadAddIn:
    MsgBox msg, vbCritical, "AddIn load error"
    LoadAddIn = False
End Function

I have tried altering the VSTO manifest file in the customization section below to "0" but the ClickOnce installer errors out when attempting an installation with this modification.

    <vstov4:customization>
      <vstov4:appAddIn application="Excel" loadBehavior="0" keyName="PITA Ribbon">
        <vstov4:friendlyName>PITA Ribbon</vstov4:friendlyName>
        <vstov4:description>PITA Ribbon</vstov4:description>
        <vstov4.1:ribbonTypes xmlns:vstov4.1="urn:schemas-microsoft-com:vsto.v4.1">
          <vstov4.1:ribbonType name="PITAAddIn.PITARibbon, PITA Ribbon, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
        </vstov4.1:ribbonTypes>
      </vstov4:appAddIn>
    </vstov4:customization>

Is there a way to have a ClickOnce installation set the default load behavior to "0"?

Any guidance on how to do this is greatly appreciated!

goast
  • 11
  • 3

2 Answers2

0

You need to re-sign the deployment any time you change the manifest directly after you publish it.

MSDN documentation

One of my answers that has a powershell script that automates this

Chris
  • 3,400
  • 1
  • 27
  • 41
0

The question posed by Chris in the comments section in the initial post identifies the core issue (i.e. Did you re-sign the deployment after changing loadBehavior to 0?)

Once the manifest file is modified for the Add-In, one must re-sign the manifest and assembly.

Instructions for modifying the VSTO deployment properties are addressed here

Instructions for re-signing assemblies and deployment manifests are addressed here

Generating self-signed certificates can be accomplished with PowerShell, this is the link that I used to create a code signing certificate for a test environment.

My steps were as follows:

  • Generate a new code signing certificate using PowerShell. komsky provides a great command line example.
  • After publishing the VSTO Add-In make modifications to the Add-In assembly manifest by LoadBehavior to 0
  • Use MAGE.EXE to sign the manifest mage.exe -sign "PITA Ribbon.dll.manifest -CertFile "C:\Certificates\cert.pfx -Password ******** -a sha256RSA
  • Use MAGE.EXE to update the VSTO deployment manifest mage.exe -update "C:\PitaDeploy\Pita Ribbon.vsto" -appmanifiest "Pita Ribbon.dll.manifest" -CertFile "C:\Certificates\cert.pfx -Password ******* -a sha256RSA

After performing these steps the VSTO installed with no issues.

goast
  • 11
  • 3