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

Creating and deploying Managed COM add-ins with VB.NET 2005 – Part IV
This is the fourth 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

For all code posted here I have intentionally tried to avoid a) the core .NET-approach and b) the use of classes. Instead I have tried to focus on clarity and to use a simple classic VB/VBA -approach.

The Notes Tool – Handle worksheets
The way the tool handles worksheets is similar to how it handles workbooks. Therefore only code behind the form that is unique for worksheets is presented. Below is the form that that provides end users with an interface to populate required parameters:

Notes Worksheets

The code
Make sure that the following namespaces exist in the form:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms
Imports System.IO ‘To handle files.

The following snippet code is part of the Load event for the form:

            ‘Populate the listbox of open workbooks.
           For Each g_xlwbBook In g_xlApp.Workbooks
                Me.ListBox_Workbooks.Items.Add(g_xlwbBook.Name)
            Next

The below event is used to populate the list of worksheets:

Private Sub ListBox_Workbooks_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox_Workbooks.SelectedIndexChanged
        Try
            ‘Clear the list.
           Me.ListBox_Worksheets.Items.Clear()
            ‘The selected workbook.
           g_xlwbBook = _
            g_xlApp.Workbooks( _
            Me.ListBox_Workbooks.SelectedItem.ToString())
            ‘Populate the list of worksheets.
           For Each g_xlwsSheet In g_xlwbBook.Worksheets
                Me.ListBox_Worksheets.Items.Add(g_xlwsSheet.Name.ToString)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message, g_CONST_TITLE)
        Finally
            If Not (g_xlwsSheet Is Nothing) Then g_xlwsSheet = Nothing
            If Not (g_xlwbBook Is Nothing) Then g_xlwbBook = Nothing
        End Try
    End Sub

The following code makes sure that only letters and numbers are used for the wanted filename:

Private Sub TextBox_TextFile_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox_TextFile.KeyPress
        If Not Char.IsLetterOrDigit(e.KeyChar) Then
            e.Handled = True
        End If
End Sub

Below is a snippet code for the Send e-mail Button:

       Try
            ‘Subfolder to store created attached workbooks.
           Const stFolder As String = “c:LotusAttachments”
            ‘Check if the subfolder exists or not. If not then create it.
           If Not (Directory.Exists(stFolder)) Then
                Directory.CreateDirectory(stFolder)
            End If
            ‘The With statement is considered to be a good practice
           ‘however here it’s not possible to use it for the Me-object.
           ‘Retrieve the subject for the e-mail.
           Dim stSubject As String = Me.TextBox_Subject.Text
            ‘Retrieve the message of the e-mail.
           Dim stMsg As String = Me.TextBox_Message.Text
            ‘Retrieve the priority of the e-mail.
           Dim stPriority As String = LTrim(Me.ComboBoxPriority.Text)
            ‘Retrieve the wanted name of the file to be created and add the path.
           Dim stFileName As String = stFolder & “” & _
                                       Me.TextBox_TextFile.Text & “.xls”
            ‘Retrieve selected workbook’s name.
           Dim stwbBook As String = Me.ListBox_Workbooks.SelectedItem.ToString
            ‘Grab the list of selected worksheets.
           Dim alWsheets As New ArrayList
            alWsheets.AddRange(CType(Me.ListBox_Worksheets.SelectedItems, ICollection))
            ‘Grab the list of recipients of Send To.
           Dim oSendTo(Me.ListBox_SendTo.Items.Count – 1) As Object
            Me.ListBox_SendTo.Items.CopyTo(oSendTo, 0)
            ‘Grab the list of recipients of Copy To.
           Dim oCopyTo(Me.ListBox_CopyTo.Items.Count – 1) As Object
            Me.ListBox_CopyTo.Items.CopyTo(oCopyTo, 0)
            ‘Call a function in a standardmodule to create and save the workbook.
           If Create_Save_Workbook( _
            stwbBook, stFileName, alWsheets) = True Then
                ‘Call a function in a standardmodule to reate and send the e-mail.
               If Create_Email_Notes( stPriority, stSubject, oSendTo, _
                oCopyTo, stMsg, True, stFileName) = True Then _
                    MessageBox.Show _
                     (“The e-mail was successfully created and has been sent.”, _
                    g_CONST_TITLE, MessageBoxButtons.OK)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, g_CONST_TITLE)
        Finally
            Me.Close()
        End Try

In the next post the functions etc in the standard module are presented which ends the presentation of the Notes Tool.

Kind regards,
Dennis

Posted in Uncategorized

7 thoughts on “Creating and deploying Managed COM add-ins with VB.NET 2005 – Part IV

  1. Ross,

    Thanks for the URL and Your kind comment :)

    >Sure looks like .net needs a lot of work to get it up and running.

    Yes, and it will be even more complicated when we add additional technical information when it comes to deployment and codesigning.

    I hope You and other visitors can maintain the interest as I will in the end compare VB.NET with VB 6.0 when it comes to COM add-ins where I hope I can present some guidelines.

    The folling list shows the headlines for remaining post on the subject:

    1. NotesTool – Functions
    2. Com Shim Wizard
    3. Security (Strong names and CodeSigning)
    4. Deployment
    5. VB 6.0 vs VB.NET 2005 – Creating & Deploying COM add-ins

    Kind regards,
    Dennis

  2. Mike,

    Many thanks for Your kind comment :)

    From what I can understand it looks like we:
    – For 2000 to 2003 will still need to use a ‘shimmed’ DLL *if* we use the .NET platform while
    – For 2007 and forward it will rely on manifest which does not require any shimming at all.
    (If I understand it correctly this is already an approach when it comes to MS Outlook).

    Usually MSFT don’t give anything for free so I guess that by making it available for free they hope that more developers will find their way to the .NET platform.

    Kind regards,
    Dennis

  3. Dennis, Mike,

    I intend to download the trail of VS2005 pro, and set aside some time to follow your instructions,
    I am looking forward to the shim section, I’ve never been able to understand this!

  4. Ross,

    Compared with VBA it’s a total different ‘world’ which is also true to some extend when it comes to classic VB. I’ve never said that .NET stuff is simple…

    In my opinion the MSDN articles I refer to (see http://www.dailydoseofexcel.com/archives/2006/08/16/creating-and-deploying-managed-com-add-ins-with-vbnet-2005-part-vi/) are good although they don’t describe how to put the generated standard COM DLL and the ‘shimmed’ managed COM add-in into an installation package. The later will be subject for an additional post by me.

    Kind regards,
    Dennis


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.