VSTO & .NET & Excel

September 13, 2007

.NET Co Library – Part I

Filed under: .NET & Excel, COM Add-ins — Dennis M Wallentin @ 11:08 pm

This is the first post about my new (coming) free managed COM Add-in “.NET Co Library” for MS Excel 2000 – 2007.

What .NET Co Library is

For a longer time I have used a similar tool (for internal use only) to store created VBA code and to reuse all the VBA code. Originally I used the CodeLibrary Access database that was shipped with the Microsoft Office Develop Edition (MODE). After some time I developed my own tool for it and at the same time I ported it to a SQL Server 2000 database. 

The Add-in is also a revised and updated version of the  .NET Connection Tool. When .NET Co Library is shipped then this tool will no longer be available for download.

The acronym “Co” in its name refers to Code and to Connection.

With the tool we can:

  • Create connection strings to a various number of databases via two wizards, the .NET Wizard and the Data Link Wizard.
  • Store the created connection strings in a structural way enabling us to reuse the connection strings in all kind of VBA solutions.
  • In an organized way store VBA code, code snippets / procedures / modules, and SQL Queries which enabling us to reuse the code when wanted.

The Add-in uses an Access database to store all the data in. However, I have an idea about allowing using another database (SQL Server, MySQL et al) in a future update.

When the Add-in is installed a new menu item is added to the standard toolbar in the VB Editorn. The three main parts of the tool are:

  • Code

  • Connection 

  • Maintenance

 Code

The following screenshot shows the commands available under the Code section.

code.png

To add a code snippet to the database we first select the wanted code and then select the command Code Snippet. In the next step a wizard guide helps us further.

In the active Code Window a new sections is added to the menu – Code – when we right-click in the window. The following screenshot shows the available commands:

codewindow.png


Connection

The Connection section contains the .NET Connection Tool’s solution (where the 255 limit no longer exist for connection strings – More than 32000 characters are now allowed for the connection strings). The following screenshot shows the available commands:

connection1.png


Maintenance

This section includes tools to maintenance the database and to make some general settings for the Code Library. The tools also makes it possible to move the database to a new location. For instance, the database can be moved to a network location where it can be shared by a group of developers. The following screenshot shows the available commands:

 

maintenance.png

 

Another aspect that I may consider in the future is to make it available for other Office programs. Since most MS Office programs (except MS Outlook) expose the same VBE object model the work to implement it in MS Word,  MS PowerPoint, MS Access, MS Project and MS Visio is rather simple. But first of all I like to make it available for MS Excel.

Finally, the development tools I use for .NET Co Library are:

  • Microsoft Visual Studio 2005 SP-1

  • Add-In Express 2007 for Microsoft .NET

  • Component One Studio Enterprise

  • MZ-Tools 2oo5 for Visual Studio 2005

  • RoboHelp

This blogpost (hopefully) present the .NET Co Library from a general point of view which I also hope is understandable. In the next blogpost on the subject focus will be set to the Code section of the tool.

As usual, comments are welcome.

Kind regards,
Dennis

Advertisements

August 31, 2007

Providing Form-Based help for Managed COM Add-ins

Filed under: .NET & Excel, COM Add-ins, VSTO & Excel — Dennis M Wallentin @ 3:20 pm

All my managed COM Add-ins solutions extensively use Windows forms. With .NET it’s very easy to create help that are directly associated with Windows forms which I hope this blogpost also will show.

For all my solutions I use complete helpfiles (.chm) created and compiled with RoboHelp  which is also used here. A compiled helpfile consists of several HTML files and the common term for these files is “topics”. Usually I place the helpfiles in the same directory as the solutions are placed in.

Basically Form-based help means that a specific topic is associated with a specific Windows form.

The following screenshot is from RoboHelp and shows the list of topics for a help project. For the case here I will be using the topic “Move Database” (its filename is Move_database.htm).

helptopics.png

In order to add Form-based help we need to add an instance of the HelpProvider to the Windows form. Like the ErrorProvider and other providers the HelpProvider is added to the Windows form’s component tray as the following screenshot shows:

helpprovider.png

When adding the component to the Windows forms five new properties are added to the form’s properties:

  • Help button:
    True if we want to add a help button to the Windows form’s caption bar to the right.
  • HelpKeyword on HelpProvider:
    Determines the Help keyword associated to the form or to the control. Here we refer to the topic filename as the below screenshot shows.
  • HelpNavigator on HelpProvider:
    Determines the kind of Help associated with the form or the control. Here we set it to Topic.
  • HelpString on HelpProvider:
    Determines the Help string associated with the form or the control. Here we leave it empty.
  • ShowHelp on HelpProvider:
    Determines if help is available or not for the form or for the control. This property should always be set to True. 

The following screenshot shows the first four properties’s settings:

helpsettings.png

When the Windows form has been loaded and has the focus the HelpProvider component starts to monitor the Windows form. When the end users press the F1 key the named topic is loaded in its own window. 

In the property window for the HelpProvider we also need to add the filename to the HelpNamespace:

help.png

What should be notable is the following:

  • No code is used to get the above solution.
  • In .NET we can refer to topics and are not forced to use TopicIDs.
  • No code or any settings are used to hook the help to the F1 key.
    It can be discussed whether the use of F1 key is the best approach or not. A help button can be a better solution under some other conditions.
  • For Windows forms that have multiply purposes the Form-based help approach should be replaced with a Control-based approach.

To see the final result of the above please see helpaction.png

Kind regards,
Dennis

August 30, 2007

Great COM Add-ins examples

Filed under: .NET & Excel, COM Add-ins, VSTO & Excel — Dennis M Wallentin @ 12:47 am

In MS Outlook it’s not possible to create native Add-ins as we can do in Excel. The only way is to create managed & unmanaged COM Add-ins. The first program that it was possible to create VSTO Add-ins for was Outlook. In my opinion these circumstances has lead to a situation where the group of professional Outlook developers has taken the lead when it comes to developing COM Add-ins.

One of the well known Outlook developers in the online community is Ken Slovak and his latest book Professional Outlook 2007 Programming (or at Wrox’s site) will soon be available. The projects and templates that are used in the book has the publisher (Wrox) agreed to make available for public download.

For some days ago I downloaded the VB.NET / VSTO 2005 SE projects and samples and spent time to evaluate them. Although we may not be interested to develop Add-ins for Outlook I find them to be great examples for creating robust and reliable managed COM Add-ins projects.

The projects and templates are available for download from Ken’s site:
Outlook 2007 Projects & Templates

Kind regards,
Dennis

August 21, 2007

FolderBrowserDialog and Managed COM Add-ins

Filed under: .NET & Excel, COM Add-ins, VSTO & Excel — Dennis M Wallentin @ 5:51 pm

I’m working with the new version of .NET Connection Tool and discovered an odd thing. In a Windows Forms I have added a FolderBrowserDialog enabling users to select a new folder to move the Access database to.

The code in use is the following:

folder.png

As you can see the code disables the button to create new folders with. When using it in Windows Vista & Excel 2007 the folder browser’s form is showed correctly as the following image shows:

2k7.png 

With Windows XP and Excel 2002 and Excel 2003 it also works properly. However, when using it in Windows 2000 & Excel 2000 the option to create a new folder is now available:

2k.png

Since I’m not been able to resolve it myself I wonder if anyone know how to solve it so that the button New Folder is also not available in Excel 2000?

Kind regards,
Dennis
 

July 8, 2007

The bridge over trouble water

Filed under: .NET & Excel, VSTO & Excel — Dennis M Wallentin @ 2:32 pm

Introduction 

For the larger group of .NET developers they never need to work with Excel. The same can be said for the larger group of Excel developers, i e they never need to work with .NET. 

So for these two groups they can focus on to increase their knowledge and skill with their primarily development tools.

But it does exist a group of developers that need to control Excel through .NET for various reasons. Some are forced to do it due to customer’s requests while it for a small group of developers is done on a voluntarily basis.

Controlling Excel through .NET?

For some people it’s obvious what controlling Excel through .NET’ stands for while for other it’s more unclear.

Developing solutions that control Excel in one or another way is done with .NET tools like:

  • ASP.NET
  • C++
  • C#
  • VB.NET
  • VSTO 2005 and VSTO 2005 SE (C# and VB.NET)

To retrieve data from centralized RDBMS it requires knowledge about, ADO.NET, SQL (and later on about LINQ) as well as a general knowledge about RDBMS.

It should also be pointed out that a general knowledge about .NET Framework and its security system is a must to create and deploy .NET solutions.  For deploying VSTO solutions it’s, at present, a quite demanding process. As for the security aspect it will become more obvious when we all move to the Windows Vista platform.

So when viewing .NET platforms and its tools from an Excel developer’s perspective it’s quite challenging to approach it and to use it.

Excel and its object model is also not easy to understand and differ in many ways compared with how we, for instance, work with objects et al in VB.NET.

Writing effeciency code that targeting Excel’s object model is also a difficult task to achieve. When Excel workbooks are used as data sources it requires knowledge on how to use classic ADO. Sometimes it’s necessary to create native XLAs that control .NET solutions, which of course require knowledge about VBA.

In the same way as .NET is challenging for Excel developers it’s for .NET developers also challenging to work with Excel.

In this context I usually classify .NET based solutions into one of the two following categories:

  • Automation of Excel
    – Using Front Loaders
    – Creation of workbook’s based reports (with charts, tables and calculations)
  • Managed COM Add-ins and managed Automation Add-ins

All in all, to create robust and reliable solutions where .NET communicates with Excel requires good knowledge and skill within both these two areas.

To be honest it’s just a handful individual in the online community who can measure up to have the required knowledge and skill with .NET & Excel.

The bridge over trouble water 

For both .NET and Excel developers it exist many public forums where they can get help to solve technical problems. In addition, by “googling” they can also search and find solutions from various sources including specialized sites and blogs et al.

For developers that need help to solve problems with .NET & Excel there exist a few public forums as well as a limited number of other available online resources. In other words, the bridge over trouble water  is not that large but it’s solid.

One forum is MSDN’s VSTO forum which explicit target the VSTO technology for the Office suite. The forum covers the whole Office suite including InfoPath.

Another forum that explicit targeting .NET and Office is Xtreme VB Talk’s forum .NET Office Automation. It also includes the best online resources to understand more about VB.NET Office Automation especially when it comes to Excel.

What makes this forum unique is that it’s managed by one individual, Mike Rosenblum, who’s strongly devoted to this area, in particular VB.NET and Excel. He is also the author to all the available tutorials, FAQ and guidelines that can be found at Xtreme VB Talk.

Mike has also authored two blogposts here (Dealing with CVErr Values in .NET – Part I: The Problem and Dealing with CVErr Values in .NET – Part II: Solutions). 

When Mike last week told me that Microsoft has award him the MVP Excel title I was very pleased. It’s not only on behalf of Mike but also on behalf of .NET & Excel.

Since Mike only work with .NET & Excel his MVP status is also a great milestone for this area. It will give this area higher status and it’s also an indication that Microsoft put a value into it.

So this blogpost is dedicated to Mike and to all the other more anonymous devoted individuals in the online community who provide the bridge over trouble water. 

Finally, as You may know or not know this blog together with my English site ExcelKB are devoted and focused on .NET & VSTO & Excel.

Kind regards,
Dennis

April 8, 2007

Requirements VSTO solutions

Filed under: VSTO & Excel — Dennis M Wallentin @ 1:49 am

It seems that it’s still existing some confusion regarding what it actually requires to develop and use VSTO solutions and also which versions of Office are supported. This blogpost will try to give a better overview of VSTO.

At present there exist two versions of VSTO, 2005 and 2005 Second Edition (VSTO 2005 SE).

VSTO 2005 can either be used standalone or ‘on top’ of Visual Studio 2005 Professional and higher. It requires that we buy a license of it in order to use it.

With VSTO 2005 we can:

  • Create workbook’s level solutions, i e standalone workbooks and templates with managed code (VB.NET / C#) behind for Excel 2003 (and Excel 2007).

VSTO 2005 SE is available free for download. In order to use it we either need VSTO 2005 or Visual Studio 2005 Professional and higher. 

What we can do with VSTO 2005 SE:

  • Create managed COM Add-ins (VB.NET/ C#) for Excel 2003 and Excel 2007

The following list shows the requirements on the development computer:

req.png

The following list shows the required Office version for each version of VSTO (and on the development computer):

msoupdate.png

Note 3 above is important as we cannot explicit use VSTO 2005 with Excel 2007 to develop workbook’s level solutions. The workaround is to use Excel 2003 and then test the solutions with Excel 2007.

Note 4 above, I did test to have Office 2003 and Office 2007 installed side by side together with VSTO 2005 SE but it only ended up in a critical error message.

The following list shows the requirements on the targeting computers:

 ta.png

Finally, I have an article, that is regular updated, about online resources for VSTO here which includes all the relevant links for the above. 

Kind regards,
Dennis

March 26, 2007

Conditional Formats

Filed under: VSTO & Excel — Dennis M Wallentin @ 7:37 pm

For some days ago I received an e-mail about creating conditional formatting through code which I sent a reply on. The next e-mail asked specifically about when to create these formats via code.

I can only answer for myself and the below list gives some indication when I use it, i e creating conditional formattings, via code:

  • To highlight various groups of data in pivottables, lists and tables. Usually the raw data is retrieved from database(s).
  • Creating standalone workbooks and/or templates through code where it’s necessary to highlight data in specific areas.

In general I try to avoid other situations then above as I find it complicated to work, through code, with conditional formatting under other circumstances.

With Excel 2007 we now have several new conditional formats to use.  However, the new conditional formats also bring ‘new rules’ for working with them in code. Therefore we need to be aware of it and, of course, know how to work with them.

The following code example shows how we work with one of the old conditional formats, adding a conditional formatting based on an expression, in VSTO:

cfold.png

The most interesting aspect in the above sample is the declaration of the variable p_cfOld as an Excel.FormatCondition’s object.

The following code example shows how we work with one of the new conditional formats, adding a conditional formatting based on the Icon Sets, for Excel 2007 in VSTO:

cfnew.png 

As the above sample shows, we now use the Icon Set’s object instead of the general FormatCondition’s object. If we should try to use the FormatCondition’s object it would end with an exception.

The new conditional format’s objects seem not to be part of the general FormatCondition’s object. Instead the new conditional formats have their own collections and therefore we need to explicit declare the format type(s) involved for specifik tasks. However, as the above samples show, they seem at least to be part of the range object’s conditional format collection.

Kind regards,
Dennis

February 10, 2007

Books on VSTO

Filed under: VSTO Books — Dennis M Wallentin @ 3:24 pm

The present VSTO books 

I thought that we this week would take a break from the (“never ending”) VSTO case and instead review a list of available books on VSTO. If I had missed any book on the subject please let me know about it.

VSTO itself can be viewed as a wide and technical complex area. Not only because it target nearly all the softwares in the Office suite but it also target the server side Office solutions. Since it operates on the .NET platform we have the whole .NET environment to consider and we should not forget the deployment aspect of VSTO.

As we already know, the Office suite is also a huge area to cover. So when putting VSTO together with the Office suite (that is 2003 and 2007) we realize that it’s at the same time both a complex and huge area to cover.

Looking on the targeting groups of developers it exist two major groups that per se are interested in the field of VSTO and Office:

  • Office developers who need to know more about using .NET & VSTO together with their developing platform(s) like Excel or/and Word or/and Outlook.
    This group of developers knows well the software’s object models in the Office suite but usually very little when it comes to .NET platform in general and VSTO in particular.
  • Corporate developers who need to know more about using the tools included in the Office suite together with .NET & VSTO.
    This group of developers know well .NET platform and do understand the basic underlying technical premises for VSTO. But at the same time they lack knowledge about the software’s objectmodels in the Office suite and also how to fully leverage the Office suite’s components.

The above summarize “what” and “who” when it comes to the framework for reviewing the available VSTO books.

People who know me also know that I prefer books that cover small and specific areas.

Title: Microsoft .NET Development for Microsoft Office
Author: Andrew Whitechapel (Microsoft Corp)
Publishing year: 2005

This book has a strong focus on COM Add-ins and VSTO. It provides deep technical information and it does assume that the readers knows .NET and are experienced programmers. VSTO is covered in three chapters and it gives an excellent description of VSTO’s load sequence. It uses C# examples for Excel and Word but does not explicit trying to cover the object models. Deployment is discussed but not in detailed.

The present edition of the book is targeting Visual Studio.NET 2003 and version 97 to 2003 of the Office suite.

In view of “what” and also “who” the book does not really fit in in the above general classification scheme.

If You, like me, is interesting in “plumbing” and to get a deeper understanding of the technical framework then this book is highly recommended. I like the strong focus and also the technical level. If You’re interested then it should be possible to buy an used copy of it.

I hope that Andrew Whitechapel will find the time to update the book so it cover VSTO 2005, VSTO 2005 SE (or even better version 3.0!) and also Office 2007. 

Title: Professional Excel Development
Authors: Stephen Bullen, Rob Bovey and John Green
Publishing year: 2005

I have previously made a review of it which is available at VBAExpress but not explicit about the VSTO chapter.

The VSTO chapter is very good and it explicit targeting the Excel developers. It discusses managed workbooks and managed COM add-ins which also include the shortcoming with version 1.0 of VSTO.

The book is per se not about VSTO and therefore VSTO is only covered in one chapter. On the other hand this is the best available introduction to managed workbooks.

Title: Visual Studio Tools for Office (Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath)
Authors: Eric Carter and Eric Lippert (Microsoft)
Publishing year: 2006

This book explicit target corporate developers and it’s also trying to cover all the mentioned programs named in the title. The outcome of it is rather good but it does not offer any possibilities to dig deeper into the central subjects.

The book gives a good introduction to security (one chapter) and deployment (one chapter) but does not explicit discuss any troubleshooting. It also discusses managed COM Add-ins but only in terms of Outlook add-ins as the VSTO 2005 SE was not available when the book was written. The Server side of VSTO is also introduced in one chapter. For obvious reason the book does not cover Office 2007.

If we have basic knowledge of .NET platform and also have basic knowledge of all the softwares in the Office suite then this book is of high interest.

In my opinion this is the best VSTO book available although it does not gives an introduction to .NET platform and more or less only discuss event programming.

Title: Professional VSTO 2005
Author: Alvin Bruney (MVP ASP.NET)
Publishing year: 2006

In general this book gives hands on examples for working with VSTO and with Excel, Word and Outlook. Most examples are targeting Excel and it offer both VB.NET and C# code for all the examples. This is only the second book which discuss the shortcoming of VSTO compared with VBA. 

I find it extremely remarkable that a VSTO book can both avoid discussing security and also deployment in more detail. I also find the coding technique to be of low standard.

All in all, don’t buy this book.

Title: VSTO for Mere Mortals
Authors: Mathleen McGrath and Paul Stubbs (Microsoft Corp)
Publishing year: 2007

This book explicit targets the group of Office developers and the softwares Excel, Word and Outlook. It gives a light weighted introduction to the .NET platform and it also gives a light weighted introduction to VSTO. All the examples are light weighted and it appears that it there exist no issues at all. The discussions about security and deployment are also light weighted and apparently there exist no problems in these two fields. The discussion about the server side of VSTO is also light weighted.

The book is the latest available and was written during the time when VSTO 2005 SE was shipped and also when Office 2007 was shipped. I expected that the book would give us more about managed COM Add-ins and about VSTO & Office 2007 but unfortunately not. Instead we are offered a light weighted chapter about Office 2007.

If You want a well written light weighted book about VSTO and find review questions that end each chapter to be of high interest then this book is for You.

I find it disappointing that the two authors, who claim to have been real world VBA developers before they joined Microsoft, present and discuss VSTO in terms of what it can do instead of how to apply it in real world developing.

Final words

From a strictly professional point of view I really like VSTO. The more I work with VSTO the more I also realize its great potential. However, we still face big issues when it comes to the areas of security and deployment. It also needs some additional versions before its bullet proof and fully developed, i e ready for real world developing.

The books on VSTO are mainly written by co-workers at Microsoft and I have the impression that the books, in the first place, are used as marketing channels for VSTO.

If Microsoft wants the Office developers to port themselves to VSTO then it’s a must that all books written by Microsoft explicit help the Office developers in terms of real world developing.

In general I find the ” VSTO and Office” approach to be too wide as it don’t provide the readers with a depth and with a focus on specific issues for the individual softwares, i e troubleshootings.  

I hope that the well known book authors of Excel, Word and Outlook will include chapters about VSTO with real world aspects in their upcoming books. In that way VSTO will get a better platform to be reviewed from and the book buyers can get knowledge and solutions to add to their development knowledge toolbox.Kind regards,
Dennis

December 28, 2006

Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part I

Filed under: VSTO & Excel — Dennis M Wallentin @ 8:21 pm

This blogpost, together with some upcoming blogposts, will end the series “The Transition Case” by discussing and viewing the Visual Studio 2005 Tools for Microsoft Office System Second Edition (VSTO 2005 SE) based solution. 

Since I have decided to set focus on VSTO & Excel during 2007 this blogpost is also a start for the series “Creating and Deploying Managed COM add-ins. However, it will not prevent me from continue to discuss .NET & Excel from a broader perspective. I will also later cover how to create and use classic commandbars in Excel 2003 with VSTO 2005 SE solution.  

Before “diving into the world of VSTO” I would like to remind that my recommendation VSTO 2005 SE is still valid mainly due to the present deployment issues. However, I sincerely hope that when version 3.0 of VSTO is shipped these issues have also been solved in a developer friendly way.

#Introduction 
VSTO 2005 SE was released in November 2006 and it provide us with the possibility to create application level add-ins, i e managed COM add-ins, for Excel 2003 and Excel 2007.

The following requirements must be met in order to create and distribute VSTO based add-ins:

  • Visual Studio 2005 Professional or higher or
  • Visual Studio 2005 Tools for Microsoft Office System (VSTO 2005) and
  • Visual Studio 2005 Tools for Microsoft Office System Second Edition (VSTO 2005 SE) and
  • .NET Framework 2.0 and later (also on the targeting computers)
  • VSTO 2005 SE runtime (on the targeting computers)
  • Excel 2003 (certain versions only) and Excel 2007 (all versions)

Special note:
Microsoft strongly recommend to not install VSTO 2005 SE on any computer that have more than one version of Microsoft Office installed.

#Registry entries
One important aspect is that we don’t need to shim the add-ins like we need to do with  Shared Add-ins (developed with VB.NET) since VSTO use an add-in loader file.

The following (Swedish) screenshot shows the entry in the HKEY_CLASSES_ROOT section and under the InprocServer32 subkey:

addinloader.png

Compared with managed/unmanaged COM add-ins the registry entries in the HKEY_CURRENT_USER is the same except that VSTO managed COM add-ins also got a Manifest entry (that points to the Manifest.DLL file for the Add-in) as the following (Swedish) screenshot shows:

manifest.png

 # The Project
The project involved is the same as for the previously Transition case although it does not cover subclassing and reading/writing to the Windows registry.

The following screenshots shows the Solution Explorer for the project:

solution-explorer.png

As we can see it contain a great number of Detected Dependencies.

#The Created skeleton
When creating the VSTO project with the add-in template for Excel 2007 it creates a class module – ThisAddin.vb – which contain only two procedures: 

thisaddin.png

In my opinion this is a welcome improvement as this two events are the ones we usually had used in unmanaged COM Add-ins.  The ExcelLocale1033Proxy class is interesting as it provides two methods we can use to modify the locale ID for individual Excel objects, i e wrap for locale ID 1033 (English United States) and unwrap for another Locale ID, for instance Swedish. An even better approach is to use ExcelLocale1033Attribute which allow us to pass all the native Excel objects locale ID and in that way consider the local regional settings (which was also pointed out as a shortcoming in previously version by Bullen et al in their book “Professional Excel Development”).

In the next blogpost I will discuss more in detail the project itself.

Kind regards,
Dennis

September 22, 2006

About VSTO &.NET & Excel

Filed under: — Dennis M Wallentin @ 10:17 am

The blog is about developing solutions with VSTO & .NET that targeting Excel. The focus is primarily set on how to leverage the developing tools on the .NET platform but it will also explore the options to work with Excel and SQL Server via VSTO and .NET

For You information:

  • All of the files available for download, code and recommendations are provided as ‘as-is, whithout any warranty or support. All use is at Your own risk.
  • Please don’t post questions about issues You have unrelated to the individuals blogposts. All this kind of comments will be deleted without further notice.

Kind regards,
Dennis Wallentin aka XL-Dennis

2010 – 2011

Blog at WordPress.com.