VSTO & .NET & Excel

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

Advertisements

2 Comments »

  1. Well done Mike, very much deserved. Also thanks to you Dennis for your input in this area too.

    Comment by Ross — July 13, 2007 @ 3:05 pm

  2. Thanks Ross 🙂

    It’s more to come when next version of VSTO is out. Meanwhile I got some additional blogposts on working with VSTO and Excel’s built-in tools.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — July 13, 2007 @ 6:41 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: