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:
The code
Make sure that the following namespaces exist in the form:
Imports System.Windows.Forms
Imports System.IO ‘To handle files.
The following snippet code is part of the Load event for the form:
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:
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:
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:
‘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
Excellent work Dennis thanks for the posts, I’m sure they’ll help a lot. I need to re-read the lot of them (a few times) to get a really good understanding! Sure looks like .net needs a lot of work to get it up and running.
Have you heard much about the new VS addin for makeing Office adding?
http://blogs.msdn.com/kathleen/archive/2006/08/09/693474.aspx
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
Very, very nice work Dennis, a really impressive amount of detail. :)
Ross, that is an excellent link. (And the official announcement, here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=459132&SiteID=1) This “Cypress” VS 2005 add-on does sound very promising. Might this finally be our easy-to-deploy shim for Office? We’ll have to wait and see, I suppose…
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
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!
[…] If you’ve been following xlDennis’ excellent set of post over at DDOE then you might be tempempted to try your hand at a .Net managed code addin. If you do, this might be of intrest to you: […]
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