Author: Anonymous

Position of icons in customized Toolbars

Posted on by Anonymous

Position of icons in customized Toolbars

Hi all,

I thought it may be of general interest to discuss where to place icons in customized toolbars.

In my experience the most general structure seems to be the following:

Standard

Edit:
Since the first comments cover also floating toolbars I add the floating resized toolbar:

General resized

However I prefer the following structure:

Commandbar

Edit:
Since the first comments cover also floating toolbars I add the floating resized toolbar:

Reszied floating

Which one is the one You prefer and why?

Kind regards,
Dennis

Customize Ribbon UI for Excel 2007 with ‘Managed’ COM Add-ins (VB.NET 2005)

Posted on by Anonymous

Customize Ribbon UI for Excel 2007 with ‘Managed’ COM Add-ins (VB.NET 2005)

For a version that cover COM Add-ins developed with classic VB please see the following post: Customize RibbonX UI for Excel 2007 with COM Add-ins (classic VB)

The Customized RibbonX ‘Manifest’
The NotesRibbon.XML file is embedded in the project and the only difference between this file and the one used in the previously post (as above) is the following:

<button id=“hlp”
label=“Help”
screentip=“Help with Notes Tool.”
imageMso=“AutoDial”
size=“large”
onAction=“NotesButton” />

Code in the Connection class
In this case I use Windows icon files and therefore they need to be converted to bitmaps before we can retrieve the IPictureDisp for them. The function GetResourceBitMap below takes care of the converting process. In order to retrieve the IPictureDisp for the bitmaps we need to get access to the protected method GetIPictureDispFromPicture in the class System.Windows.Forms.AxHost. For the case it’s solved by using a separately class which is presented after the code for the Connection class.

All the Windows icon files (32×32) are embedded and only the relevant code for the case is showed here.

Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core ‘To implement and use the IRibbonExtensibility.
Imports System.Windows.Forms ‘To use MessageBox.
Imports Extensibility ‘To implement and use the IDTExtensibility2.
Imports System.Runtime.InteropServices ‘Defines the GuidAttribute and ProgIdAttribute.
Imports System.Reflection ‘In order to use the method GetExecutingAssembly.
Imports System.IO ‘In order to use a Stream and a Streamreader.
Imports stdole ‘To work with the IPictureDisp
Imports System.Drawing ‘To work with the images.

Public Class Connect
    ‘In order to customize the Ribbon UI the IRibbonExtensibility interface must be implemented.
    Implements Office.IRibbonExtensibility
    Implements Extensibility.IDTExtensibility2

    Public Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection
        g_xlApp = Nothing
    End Sub

    Public Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection
        g_xlApp = CType(application, Excel.Application)
        System.Windows.Forms.Application.EnableVisualStyles()
        System.Windows.Forms.Application.DoEvents()
    End Sub

    Function GetCustomUI(ByVal ribbonID As String) As String Implements Office.IRibbonExtensibility.GetCustomUI
        ‘Since the NotesRibbon.xml is embedded we need to fully qualify the resource name:
        ‘<appnamespace>.<filewithextension>.
        Return GetResourceXML(“Notes.NotesRibbon.xml”)
    End Function

    Public Function GetResourceXML(ByVal resourceName As String) As String
        ‘Set a reference to the assembly during runtime.
        Dim asm As Assembly = Assembly.GetExecutingAssembly()
        ‘Declare the textreader.
        Dim xmlDataReader As StreamReader = _
            New StreamReader(asm.GetManifestResourceStream(resourceName))
        With xmlDataReader
            ‘Read and return the XML file in a chunk.
            Return .ReadToEnd()
            .Close()
        End With
    End Function

    ‘The Callback function for the customized Ribbon Notes Tool.
    Public Sub NotesButton(ByVal control As Office.IRibbonControl)
        Try
            Select Case control.Id
                Case “wbk” : Show_Form(“wbk”)
                Case “wst” : Show_Form(“wst”)
                Case “hlp” : Process.Start(My.Application.Info.DirectoryPath & “Notes_Tool.chm”)
                Case “abt” : Show_Form(“abt”)
                Case “xld” : Process.Start(“http://www.excelkb.com”)
            End Select
        Catch ex As Exception
            MessageBox.Show(ex.ToString, g_CONST_TITLE)
        End Try
    End Sub

    ‘The Callback function for the customized images.
    Public Function GetImage(ByVal control As Office.IRibbonControl) As IPictureDisp
        Dim pictureDisp As IPictureDisp
        pictureDisp = Nothing
        Select Case control.Id
            Case “wbk” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“workbooks.ico”))
            Case “wst” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“worksheet.ico”))
            Case “abt” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“info.ico”))
            Case “xld” : pictureDisp = ImageAxHost.Image_Convert(GetResourceBitmap(“xldennis.ico”))
        End Select
        Return pictureDisp
    End Function

    ‘Function to convert Windows Icons to Bitmaps.
    Public Function GetResourceBitmap(ByVal resourceName As String) As Bitmap
        ‘Set a reference to the assembly during runtime.
        Dim asm As Assembly = Assembly.GetExecutingAssembly()
        ‘Since the icon files are embedded we need to fully qualify the resource names, i e
        ‘<appnamespace>.<filewithextension>.
        Dim stream As Stream = asm.GetManifestResourceStream(“Notes.” & resourceName)
        ‘Convert the Windows icons to GDI+ Bitmaps.
        Dim image As Bitmap = New Icon(stream).ToBitmap()
        stream.Close()
        Return image
    End Function
End Class

Code in the ImageAxHost class
By using a class, which inherits from System.Windows.Forms.Axhost, we get access to the protected method GetIPictureDispFromPicture. Below is the code in the ImageAxhost class:

Imports System.Windows.Forms
Imports stdole

Friend Class ImageAxHost
    ‘The class derives from System.Windows.AxHost.
    Inherits System.Windows.Forms.AxHost

    Sub New()
        ‘Intialize the base class to nothing.
        MyBase.New(Nothing)
    End Sub

    Public Shared Function Image_Convert(ByVal image As System.Drawing.Image) As IPictureDisp
        Return AxHost.GetIPictureDispFromPicture(image)
    End Function
End Class

The Output
The following image shows the output after that the code has been executed:

Tool

Conclusion
I find it quite easy to work with the Ribbon UI no matter if I use VBA, classic VB or VB.NET. However, I hope that MSFT will provide us with a better solution then the above when it comes to obtaining the IPictureDisp with VB.NET.

What is most notable is that when using ‘managed’ COM add-ins we can mix built-in images with customized which seems not to be the case when working with VBA or with COM Add-ins developed with classic VB.
Edit: With the recent B2TR it works very well to mix customized images with built-in images.
In view of the fact that Office 2007 will be shipped with more built-in images then any previously version I don’t see any strong need to use customized images. In that way some extra code work is saved.

In the above case and in the previous classic COM Add-in’s case I’ve created a unique tab (Notes Tool). It looks great but if we assume that the end users actually will use 6-8 add-ins and each of them have their own unique tab then it will no longer look so great. In my opinion we should use tabs in a spare some way and instead make use of the general Add-ins tab. Of course, with this tab we need to consider how much space we actually should use for our add-in(s).

Kind regards,
Dennis

Customize RibbonX UI for Excel 2007 with COM Add-ins (classic VB)

Posted on by Anonymous

Customize RibbonX UI for Excel 2007 with COM Add-ins (classic VB)

In addition to Tushar Mehta’s contributions about RibbonX and Excel I thought it would be suitable to add some knowledge from a COM Add-in perspective. However, the issues he address are also applicable to creating COM Add-ins with classic VB therefore this post is more about ‘how to’ then exploring it further. But in an upcoming post I will review some aspects when I discuss Ribbon and ‘managed’ COM Add-ins.

The Customized RibbonX ‘Manifest’

The more I work with the .NET platform and with Excel 2007 the more I appreciate the ability to create and read ‘manifests’. Actually, it reminds me of the ini files we once used on the Windows 3.o/3.1 – platform. Together with resource files it offers flexibility that also cover situations where international usefulness is important.

In addition, XML files is used with ‘managed’ COM Add-ins (developed with VB.NET 2005 and with the coming VSTO 3.0). Per se the information does not need to reside in a file, i e it can also be part of the code.

Below is the content of the XML file which also shows the ‘structure’ I prefer to work with:

<customui xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
  <ribbon startFromScratch=“false”>
    <tabs>
      <tab id=“Notes.Tab” label=“Notes Tool” visible=“1”>
        <group id=“Notes.Group” label=“Notes Toolset” visible=“1”>
          <button id=“wbk”
                  label=“Send Workbooks”
                  screentip=“Send Workbooks with Lotus Notes.”
                  getImage=“GetImage”                    
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“wst”
                  label=“Send Worksheets”
                  screentip=“Send Worksheets with Lotus Notes.”
                  getImage=“GetImage”
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“hlp”
                  label=“Help”
                  screentip=“Help with Notes Tool.”
                  getImage=“GetImage”  
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“abt”
                  label=“About”
                  screentip=“About Notes Tool.”
                  getImage=“GetImage”  
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“xld”
                  label=“XL-Dennis”
                  screentip=“Visit XL-Dennis English Page.”
                  getImage=“GetImage”  
                  size=“large”
                  onAction=“NotesButton” />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customui>

The Code in the Connection class

Below is the code for the connection class:

Option Explicit
‘Note: No error handling is implemented in the example.
‘Only the usual references are used for the project, i e Office 12 and Excel 12.
‘In order to customize the Ribbon UI the IRibbonExtensibility must be implemented.
Implements IRibbonExtensibility

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
‘The variable xlApp is declared in a standard module.
Set xlApp = Application
End Sub

Private Sub AddinInstance_OnDisconnection _
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
‘Release the object from memory.
Set xlApp = Nothing
End Sub

Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
‘Parse the XML to the Ribbon.
IRibbonExtensibility_GetCustomUI = GetRibbonXML(App.Path & “NotesRibbon.xml”)
End Function

Public Function GetRibbonXML(ByVal stXMLFile As String) As String
‘Retrieve the content of the XML file.
Dim inFile As Integer
Dim stRibbonXML As String
inFile = FreeFile()
‘Read the XML file in a chunk.
Open stXMLFile For Binary As #inFile
stRibbonXML = Space(LOF(inFile))
Get #inFile, , stRibbonXML
Close #inFile
GetRibbonXML = stRibbonXML
End Function

‘Callbacks function for the customized Notes Tool.
Public Function NotesButton(ByVal control As Office.IRibbonControl)
Select Case control.Id
    Case “wbk”: Show_Form (“wbk”)
    Case “wst”: Show_Form (“wst”)
    Case “hlp”: Show_Help
    Case “abt”: Show_Form (“abt”)
    Case “xld”: Visit_XLDennis
End Select
End Function

‘Callbacks function for customized images.
Public Function GetImage(ByVal control As Office.IRibbonControl) As IPictureDisp
‘The bmp files are added to a resource file.
Select Case control.Id
    Case “wbk”: Set GetImage = LoadResPicture(101, vbResBitmap)
    Case “wst”: Set GetImage = LoadResPicture(102, vbResBitmap)
    Case “hlp”: Set GetImage = LoadResPicture(103, vbResBitmap)
    Case “abt”: Set GetImage = LoadResPicture(104, vbResBitmap)
    Case “xld”: Set GetImage = LoadResPicture(105, vbResBitmap)
End Select
End Function

The output

The following screen shot shows the outcome of the above approach:

Customized Ribbon

Questions

In the following image the screen tip is shown for the first button:

F1 Help

Does anyone know how we can:
1. hook up the F1 button to a customized help when ‘pressing the F1-button for more help’?
2. hide it / remove it if there are no customized help available?

For a deeper discussion about RibbonX please see Jensen Harris blog.

Finally, I would like to thank John West for his recent post about RibbonX, Excel and classic VB at xtremeVBtalk.

In my next post on the subject I will cover how to do it with VB.NET 2005 and for ‘managed’ COM Add-ins.

Kind regards,
Dennis

FYI, I will not be able to monitor DDOE for the coming week but don’t let that prevent anyone from making comments ;)

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part X

Posted on by Anonymous

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part X
You can find the earlier post on the subject at the following links:
Part I – Introduction
Part II – Connection
Part III – Notes Tool – Workbooks
Part IV – Notes Tool – Worksheets
Part V – Notes Tool – Main functions
Part VI – COM Shim Wizard
Part VII – Strong Name & Digital codesign
Part VIII – Deployment
Part IX – Troubleshooting

About the walkthrough
I have had two aims with the walkthrough:
– show how we can create managed COM add-ins and the terms for it,
– show how we can deploy managed COM add-ins and how to troubleshoot them
in order to evaluate VB.NET.

Why use COM add-ins?
In my experience it’s not explicit required by the clients but in order to meet specific functional needs from the clients COM add-ins can be one technical platform to use.

For me the following aspects can be the driving forces to use COM add-ins:
– To use third part’s ActiveX controls
– To obtain a better performance (compared with VBA)
– To protect my intellectual property
– To apply a functional licensing model

Creating and deploying COM add-ins with VB.NET or with classic VB 6.0?
For many technical reasons I prefer the .NET platform and Visual Studio.NET but that’s not the subject here. Instead the question is raised for what is best for the clients and in view of their IT-platforms.

The .NET Framework is still under rapid developing and the same is indeed valid for the Visual Studio.NET. The rapid developing itself may be a heavy reason to simple wait with the transition, especially when it comes to managed COM add-ins. Different managed COM add-ins can actually also require different versions of the .NET Framework.

For Windows 2000 and Windows XP the .NET Framework is not available unless it’s installed as an upgrade. As a consequence it will be some additional costs to calculate with in order to use managed COM add-ins if not the correct version of .NET Framework is installed. (At present version 1.1 and 2.0 of .NET Framework can coexist on the same computers.)

The same scenario as above is applicable when it comes to the PIA. However, the version for Excel 2003 cannot coexist with the version for Excel 2002 on the same computers. As long as the PIAs are version specific we cannot use early binding when several versions of Excel are involved.

For me, as a micro consultant, it will never be possible to suggest a worldwide company that they need to upgrade about 3000 computers globally in order to use my managed COM add-ins.

In my opinion it’s at present not an easy task to achieve and maintain a 100 % controlled .NET environment.

(In my opinion the above also explain to a high degree why we not yet have seen any commercial managed COM add-ins.)

From a strictly technical point of view it can be concluded that as long as Excel only communicate via a COM-interface it will be necessary to add an extra ‘layer’ (loader) between Excel and the managed COM add-ins. After all, we cheat on Excel that it ‘talks’ to another COM source and at the same time we cheat on .NET that it actually ‘talks’ with another NET source. Per design it exist a performance penalty which cannot be disregarded.

From my professional point of view the following summarize my recommendation:
As long as Windows operating systems still support classic VB 6.0 solutions and as long as Excel only offer a COM-interface to communicate via then it’s advisable to create COM add-ins with classic VB 6.0.

My recommendation should not prevent anyone from exploring and learning about the .NET platform. After all, in the long run it’s not a question if we should use it or not, it’s only a question of when as .NET is here to stay.

Pilots
At present I’ve got two managed COM add-ins ‘up & running’. In both cases it’s only for testing purpose (and the projects are considered as ‘mutual benefit’ projects between me and the clients).
– A more advanced version of the presented Notes Tool (runs on two computers)
– A tool that feed Excel 2003 with data from a SQL Server 2005 (runs on three computers)

Final words
For those of You who don’t have the option to use classic VB 6.0 the case here together with my good friend, Mike Rosenblum, excellent VB.NET Office Automation FAQ hopefully can give You some input for VB.NET & Excel.

Kind regards,
Dennis

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part IX

Posted on by Anonymous

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part IX
You can find the earlier post on the subject at the following links:
Part I – Introduction
Part II – Connection
Part III – Notes Tool – Workbooks
Part IV – Notes Tool – Worksheets
Part V – Notes Tool – Main functions
Part VI – COM Shim Wizard
Part VII – Strong Name & Digital codesign
Part VIII – Deployment

Troubleshooting
This is by no mean an easy area and the available utilities place a high demand on the developers who need to use them. Therefore make sure You know at least how the tools work and what can be achieved with them.

As for troubleshooting code it’s a too large area to be covered here but in general the ‘standardways’ for tracking down code issues should be applied togehter with the tools .NET offers.

‘Avoid …’
A good start is to avoid most articles that MSFT has written on the subject as it may be very confusing. Read the COM Shim articles mentioned here and You’ll be fine.

Since we use a standard COM DLL as a ‘loader’ and as a ‘bridge’ there is no need to ‘release’ the objects by using Marshal.ReleaseCOMObject or Marshal.FinalReleaseComObject. Set the variable objects to ‘nothing’ and move along.

‘LoadBehavior’
By default the loadbehavior is set to 3 (Connect | Bootload) and therefore it should load when the host (Excel) is launched.

‘Blacklisted’
When debugging managed COM add-ins they may not be available as expected in Excel (due to the fact that they may cause Excel to crash and therefore get ‘blacklisted’ by Excel). If this is the case then choose the command Help | About Microsoft Office Excel | Disabled items… in Excel and restore the add-in and then change the code accordingly.

‘Deployment issues’
It exist several reasons why a deployment don’t work as expected on one or more targeting machines. Here I cover some important aspects to regard when trying to troubleshooting the issue(s).

# In general it’s recommended to make sure that the latest updates of Windows, Excel, the related PIAs and .NET Framework are available on the targeting machines. It also include any fixes to the .NET Framework like KB908002.

# Are the permissions correctly set on the target machines?
By default the .NET environment give us one code group named ‘All Code’ that is associated with the ‘FullTrust’ permission set. However, it does not necessary mean that it’s the case in the environment Your solution will be implemented in, especially if the .NET Framework is already in use.

Permissions sets are maintained at the machine and user levels through the tool, Caspol.exe (Code Access Security Policy), but it’s beyond this post to dig into it any deeper. However, there are two aspects that may be of importance here a) view the ‘FullTrust’ assemblies list and b) add an assembly to the group of ‘Fulltrust’.

The Caspol utility can be found in a folder like the following:
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727

To view the present list the following command is used from a command Window: caspol -listfulltrust
as the following picture shows:

Caspol.exe

To add the ‘shimmed’ managed COM add-in’s file to the list the following command should be used:
caspol –addFullTrust c:DevNotesNotesTool.dll

For an overall view of the security You can execute the mscorcfg.msc which is located in C:ProgramMicrosoft Visual Studio 8SDKv2.0Bin and it provide a graphical interface:

Config Security

# Manually installation
If You suspect that the installation package doesn’t work as expected then one approach is to install the solution manually.

The following steps show how to ‘install’ the standard COM DLL:
1. Copy the files into a folder on a target machine.
2. Register the generated standard COM DLL with the regsvr32.exe.

The following steps show how to ‘install’ the ‘shimmed’ managed COM add-in without the standard COM DLL:
1. Copy the file into a folder on the target machine
2. Register the ‘shimmed’ managed COM add-in with the regasm.exe. For more information about the utility please see Assembly Registration Tool.

Any error message during the installation with the installation package and which also appear during a manually installation can also be an indicator that the present installation of .NET Framework is not working properly.

# Permission Calculator tool
For more complex solution then Notes Tool the utility, PermCalc.exe, can estimate the permissions that would be required for the application to run on targeting machines. It’s located in the C:Program FilesMicrosoft Visual Studio 8SDKv2.0Bin folder and in some cases under the Administrator’s folder in Document and Settings. For more information please see PermCalc

# Reinstall .NET Framework
Imagine that we have made all necessary steps correctly and the solution has been successfully deployed on all machines except for one where we receive an obscured errormessage upon installation. With this scenario there are no other alternatives (as far as I can understand) then to simple reinstall the .NET Framework.

# Access the tools
In order to use the Framework Configuration Tool and the Permission Calculator tool (together with some other tools) the .NET Framework SDK needs to be installed. For more information please see SDK.

# Finally
Since it can be a rather complex situation with error trapping I would like to see that MSFT can provide us with a tool, similar to Microsoft PSS VSTO 2005 Client Troubleshooter, which can both document and track down general issues.

In my final post in the series of managed COM add-ins I will close the case with some conclusions and guidelines.

Kind regards,
Dennis

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part VIII

Posted on by Anonymous

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part VIII
You can find the earlier post on the subject at the following links:
Part I – Introduction
Part II – Connection
Part III – Notes Tool – Workbooks
Part IV – Notes Tool – Worksheets
Part V – Notes Tool – Main functions
Part VI – COM Shim Wizard
Part VII – Strong Name & Digital codesign

Some additional code for the Notes Tool
I’ve received several e-post about the code behind the ‘Recipients’-button in the two forms the tool use. There is no connection to Lotus Notes or to Domino’s global address book. It simple retrieves the e-mail address from a selected range as the following snippet code shows (the RefEdit control is not available and therefore the InputBox is used instead):

        Try
            g_xlRng = CType(g_xlApp.InputBox( _
                                  “Please select the range that  _
                                  contain the list of recipients as Copy To:”, _
                                  g_CONST_TITLE, , , , , , 8), Excel.Range)
            g_xlcell = CType(g_xlRng.Cells, Excel.Range)
            Try
                ‘Add recipients to the list of Send To.
                If Not g_xlRng.Columns.Count > 1 Then
                    With Me.ListBox_SendTo
                        .Items.Clear()
                        For Each g_xlcell In g_xlRng
                            If Not (g_xlcell.Value) Is Nothing _
                            Then .Items.Add(g_xlcell.Value)
                        Next g_xlcell
                    End With
                Else
                    MsgBox(“The list of recipients can only be in one column.”, _
                    MsgBoxStyle.Information, g_CONST_TITLE)
                End If
            Catch ex As Exception
                MessageBox.Show(ex.ToString, g_CONST_TITLE)
            End Try
        Catch ex As Exception
            ‘Leave empty – it takes care of the cancel for the inputbox.
        Finally
            ‘Release the objects.
            If Not g_xlcell Is Nothing Then g_xlcell = Nothing
            If Not g_xlRng Is Nothing Then g_xlRng = Nothing
        End Try

Test and preparations
Before doing anything else we need to test the ‘shimmed’ managed COM add-in and the generated standard COM DLL. The simplest way is to first copy the ‘shimmed’ managed COM DLL file (together with the help file) to the folder where the generated COM DLL file is placed and then start to debug it.

When the COM SHIM Wizard has created the files it has also registered the generated standard COM DLL file on the developing machines. Since it’s a standard COM DLL it uses the regsvr32.exe.

Creating the Installation Package
We can either add a Setup project to the Com Shim generated project or create a standalone Setup project. I prefer the later for clarity in the walkthrough. The Setup Wizard can be a good start, especially if we are inexperienced with .NET.

The most important aspect is to decide which files should be included in the Setup package. The following picture shows the minimal number of files for the NotesTool and as You can see a help file is also included:

Files

Since the NotesLoader.dll is a standard COM DLL we need to set the Register properties for it to ‘vsdrfCOMRelativePath’. As for the other files settings we can leave them as they are.

The following picture shows some additional entries we can add to the Setup package:

Properties Setup

Don’t forget to add the KB908002 to the prerequisities for the Setup project. When building the Setup the package will include the installation package for KB908002 but You’ll need to install it separately and before the installation of the tool itself.

Installation of the Notes Tool
Before starting the installation of the tool it’s absolutely necessary to check that:

1. The latest SP is installed for the actuall version of Windows
2. The correct version of Excel with the latest SP is installed
3. The correct version of .NET Framework is installed
4. The correct version of the PIA is installed
5. All fixes that refer to 1 to 4 above are installed on the target machines.

If all the above preparations are done then it’s just to install the tool.

Comments:
I find the deployment tool in VB .NET to be much easier and more smoother to work with then with the tool provided in VB 6.0.

In the next post I will discuss troubleshooting when things don’t work out as they supposed to do.

Kind regards,
Dennis

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part VII

Posted on by Anonymous

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part VII
You can find the earlier post on the subject at the following links:
Part I – Introduction
Part II – Connection
Part III – Notes Tool – Workbooks
Part IV – Notes Tool – Worksheets
Part V – Notes Tool – Main functions
Part VI – COM Shim Wizard

In general I’m an advocator of software’s security and therefore I see it as important to dedicate a post on security when it comes to COM add-ins as well. For a general introduction to Office 2003 security please see the following URL: Security

Using Strong Name Signatures for ‘shimmed’ managed COM add-ins
I started to make a write up on the subject but had difficulties to keep the focus on the essential parts in view of the subject for the series. I then recalled that there recently was an article published in the MSDN Magazine and I was glad when I also found it on the web. The article is good in my opinion and it’s worth reading the whole article, CLR Inside Out – Using Strong Name Signatures. At the bottom of the article Mike Downen also brings up Windows Authenticode.

Digital sign generated standard COM DLLs
This can be applied for both standard COM add-ins, for instance developed with VB 6.0, and generated standard COM DLLs with the COM Shim Wizard.

In order to digital sign DLLs we need a certificate to use which can be bought from a security company like Thawte or Verisign . I use Thawte and I’m both pleased with their certificate and with their service.

To sign a DLL we need to use the SignTool.exe in the following way:

a. Locate the SignTool.exe file on the drive
(one location can be c:Program FilesMicrosoft Visual Studio 8SDKv2.0Bin)

1. Choose Start | Run in Windows and enter ‘cmd’ to open a command window.
2. Change the path to the SignTool’s folder.
3. Enter the following command: signtool signwizard.
4. The graphic wizard should now be started and You can follow the instructions.

In order to timestamp the DLL You need to provide a Timestamp Service URL like the following: http://timestamp.verisign.com/scripts/timstamp.dll (The spelling of the dll’s name is correct)

In many companies the security level in the Office suite is set to ‘High’ and when a COM add-in based solution is installed and Excel is then open the following message is received:

Security Message

When the end users accept the certificate the company that has signed the DLL is placed in the group of Trusted Publishers which the following picture from Excel shows:

Trusted Publishers

Edit note:
For those of You who only want to test how to digital sign a DLL can use the following tools:
– the Makecert.exe, which generates X.509 certificates for testing purposes only.
– the Cert2spc.exe, which creates, for test purposes only, a Software Publisher’s Certificate (SPC) from one or more X.509 certificates.

In the next post I will cover how we deploy the ‘digital signed shimmed’ managed COM add-in.

Kind regards,
Dennis

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part VI

Posted on by Anonymous

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part VI
This is the 6th post on the subject and You can find the earlier post at the following links:
Part I – Introduction
Part II – Connection
Part III – Notes Tool -Workbooks
Part IV – Notes Tool – Worksheets
Part V – Notes Tool – Main functions

Preparation of the managed COM add-in and some house cleaning
Before moving on to the next step we need to prepare the managed COM add-in and also do some ‘house cleaning’.

#1 Remove dependencies
Open the folder ‘Detected Dependencies’ and exclude all files except Microsoft .NET Framework. The easiest way is to right click on each file and select the option Exclude. By default shared add-ins refer to some namespaces which may or may not be used. However it’s beyond the scope of this post to discuss it in more detail. The unused references can be viewed through the property window and via the button ‘Unused references’.
#2 Uncheck the option ‘Register for COM Interop’
Right click on the project name in the Solution Explorer window and select ‘Properties’. On the Compile tab scroll down to the bottom and uncheck the option.
#3 Sign the assembly
Select the Signing tab in the properties window and apply or create a new strong name key file (I will cover Strong Names in the next post).
#4 Make sure that the final version of the add-in has been saved and compiled.

The following picture shows how the project looks in the Solution Explorer windows on my developing computer:

Preparation

The mscoree.dll “hell”
Traditional COM add-ins, for instance developed with VB 6.0, are fully referenced in the Windows registry while managed COM add-ins are not. By default for all managed COM add-ins the InprocServer32 enter for each of them in the HKEY_CLASSES_ROOT points to one file – mscoree.dll. We don’t need to dig into the registry to get a confirmation of it. Via Excel’s UI it becomes clear as the following picture shows:

Unshimmed COM

There are two important major issues that need to be resolved due to the mscoree.dll ‘hell’:
– All managed COM add-ins refer to the mscoree.dll and therefore if anyone of them does not work properly all managed COM add-ins will be ‘blacklisted’ and disabled by Excel.
– The mscoree.dll cannot be digital signed as it’s a systemfile which has a negative impact when it comes to the security.

The COM Shim Wizard
The following articles at MSDN are important for three reasons, they provide us with the Wizard tool itself, a technical detailed background information is given (especially in the first article) and how to use the tool:
Isolating Office Extensions with the COM Shim Wizard (For VS.NET 2003)
Isolating Microsoft Office Extensions with the COM Shim Wizard Version 2.0 (For VS.NET 2005)

The tool is a Microsoft Visual C++ Active Template Library (ATL) COM DLL and it creates a standard COM DLL (that is an unmanaged!) which acts as a ‘proxy’ to the managed COM add-in. The Wizard provides us with a user friendly interface which guides us through the steps in a simple way (we don’t need to know C++ to create and handle the output).

What the generated standard COM DLL actually does is to:
– Make sure that the CLR Loader load the CRL in the .NET Framework
– Create a new application domain in which the managed COM Add-in is loaded into.
– Create an instance of the managed COM add-ins class that implements the IDTExtensibility2 interface.
– Act as a ‘bridge’ between Excel and the managed COM add-in.

‘Shimmed’ managed COM add-ins are isolated from each other as they don’t share the application domain with any other ‘shimmed’ managed COM add-in. The value for the InprocServer32 for the ‘shimmed’ COM add-in in Windows registry is now the name of the generated standard COM DLL. With a standard COM DLL as a ‘bridge’ we are also able to digital sign the file.

If we want to add some additional information into the standard COM DLL file we can do it by locating the ‘*.rc’ file under the folder Resource Files in the Solution Explorer window. Make sure that the final version of the standard DLL file has been saved and compiled.

It should be noticed that the tool is not supported by MSFT.

Comments:
Andrew Whitechapel et al at MSFT have made a great work with the COM Shim Wizard and also with the documentation for it.

However, I’m having difficulties to see any advantages with the above solution compared with developing a similar standard COM add-in with VB 6.0:

# 1 The necessary solution with a standard COM DLL put an extra ‘layer’ between Excel and the ‘shimmed’ managed COM add-in which has a negative impact on the performance (Excel call the standard COM DLL which in return call the underlying ‘shimmed’ managed COM add-in and reverse).

#2 Standard COM add-ins developed with VB 6.0 share the same memory area with Excel which improve the performance (so called In-Process communication). With a ‘shimmed’ managed COM add-in there will be an additional performance penalty due to the isolation by loading the ‘shimmed’ managed COM add-in in its own application domain. If I understand it right (please correct me if I’m wrong) ‘shimmed’ managed COM add-ins and Excel runs in different memory areas (so called Out-Of-Process communication).

Edit note:
I managed to find a good online source that describe the Application Domain:
Application Domain FAQ

In the next post I will discuss security in terms of using strong names and digital signing code with Microsoft Authenticode.

Kind regards,
Dennis