VSTO & .NET & Excel

April 7, 2013

Time to move on – Exit MVP Program

Filed under: .NET & Excel, .NET Books, Apps for Office, COM Add-ins, Excel, SQL Server, VSTO & Excel, VSTO Books, XLLs — Dennis M Wallentin @ 4:27 pm

Around 2005 – 2006 I peaked with MS Excel, since then I have slightly moved away from it year to year. In 2010 I was honored to become part of Microsoft’s MVP-program.

Of course, it was interesting to get another position and more closed to Microsoft in general, the production team in particularly. However, I never got excited about it as I was moving away from MS Excel. In addition, I didn’t put much efforts to support the online community in various forms.

Given the circumstances I finally took the decision to not be up for the next renewal process. Looking back I can conclude that it has been an amazing time, from the 80’s and until now. I have also achieved more than what I thought was possible. In other words, I have nothing more to proof. So I can walk away and looking ahead for new adventures in the world of softwares.

But before I close the MS Excel book I have one thing I would like to point out and to discuss. Let me first conclude that Microsoft have never really been loyal to the group of developers for MS Excel. It’s regretful as the group have a strong commitment and interest of developing MS Excel further.

In the beginning we got the macro language, XLM, which we started to use more and more. Then VBA and VB6 came and Microsoft asked us to drop XLM in favor of these two Basic languages. 10 years later VB6 was depreciated and since then VBA also risk to be depreciated. Microsoft asked us to replace them with .NET and VSTO. 10 years later .NET and VSTO face the same situation as VB6, i.e to be depreciated. Microsoft now ask us to start develop with Apps for Office. Given the short history, i.e about 20 years, it’s remarkable the number of changes Microsoft have done.

In my opinion, Microsoft’s trust capital is now below zero due to lack of loyalty Microsoft show the group of MS Excel developers. The question is not about Apps for Office rather what will come next?

But it’s no longer of interest for me. I’m moving along and I set focus on other tools and platforms. The blog will change its name and extend its contest with other tools including other platforms. Actually, I will go back to VB6, pick up new tools like PowerBasic and PureBasic which will allow me to write everything in code, including the UI.  Other tools I have picked up is Real Studio and LiveCode together with my two favorites databases; Valentina and Ninja Pro.

I have uninstalled Office 2013 and replaced it with the 2000 version. I may re install it later on in case I find Apps for Office interesting unless it has been already replaced with something new!

It has been a fantastic time in my life to be part of the online Excel community and be part of an exciting time for MS Excel. My English has been improved with >90 %.  I have some good friends around the globe . So without mention any names I would finally say:

Thank You all!

Kind regards,

May 8, 2010

Excellent Learning Tool for Open XML SDK 2.0

Filed under: .NET & Excel, .NET Books, SQL Server, VSTO & Excel, VSTO Books — Dennis M Wallentin @ 7:18 pm

For some weeks ago Microsoft announced that they had launched the final version of Open XML SDK 2.0 for Microsoft Office. With this SDK we can create and manipulate Excel workbooks in code without involving Excel and also without having Excel installed.

What comes as a bonus with SDK 2.0 is the Open XML SDK 2.0 Productivity Tool for Microsoft Office. I was very pleased and surprised to discover that this is a multipurpose tool that allows us to a) generate reflected code, i.e to see the correct code to interact with the Open XML Documents file format, b) comparing source and target files code and b) validate code. We can also via the tool easily get access to the Open XML documentation.

The process to generate reflected code is very simple:
1. Create an Excel workbook and customize it as You want the final product to look like. Save it with the Open XML file format.
2. Open the Productivity Tool and open the created Excel workbook.
3. Navigate to the part of the created Excel workbook you want the code for.
4. Hit the button Reflect Code.
5. Done!

The following screen shot shows the tool in action:

However, we cannot control which language the generated reflected code is presented to us. The output is always in C# so if we want to see the generated VB.NET code we need to convert the generated C# code to VB.NET with a tool like Code Converter from Telerik.

The blog Brian Jones & Zeyad Rajabi: Office Solutions has published an excellent map over available online (MSFT’) resources for Open XML, which can be found at the following URL Zeyad Rajabi’s Open XML SDK Blog Map.

Personally I’m still in the initially phase and up to this date I have only done five smaller Excel jobs that involved manipulation of the Open XML file format. Have You done any work that involves it and if yes what are Your experiences?  

The more I work with it and the more I explore the above tool the more attractive it becomes. The real potential, at least to me, is that with Open XML SDK installed on a server we can generate Excel Reports in a smooth and structural way without having Excel installed. 

Enjoy it!

Kind regards,

March 26, 2010

New Download Page for XL-Dennis Tools

Filed under: .NET & Excel, .NET Books, COM Add-ins, SQL Server, VSTO & Excel, VSTO Books, XL-Dennis' freewares — Dennis M Wallentin @ 4:15 pm

As a consequence of the reconstruction of ExcelKB my free tools needed a new place. I decided to add a new page to this blog enabling everyone to download the tools:

Kind regards,

January 21, 2010

Visual Studio Tools for Office 2007 – Review

Filed under: .NET & Excel, .NET Books, VSTO & Excel, VSTO Books — Dennis M Wallentin @ 11:54 am

Visual Studio Tools for Office 2007 – VSTO for Excel, Word and Outlook
(Note: The Second Edition)
Eric Carter and Eric Lippert
Target group:
.NET Developers, especially C# developers, that want to develop Office solutions based on VSTO technology.
8 (Out of 10)

Perhaps the most important news in the second edition is that it’s only available with C#  and not with VB.NET as the first editions was. Unfortunately the title no longer includes the language in use which can be very misleading for potential book buyers.

The book’s concept is built around two topics; how to work with each software’s object model and event coding. For obvious reasons it cannot cover all the objects in the softwares. Compared with the first edition the major update is  that it now cover VSTO 3.0 . The chapter about managed COM add-in has now been replaced with a chapter that explicit target VSTO add-ins and the second edition now also cover the Ribbon Visual Designer in VSTO. A complete new chapter has also been added about using the ClickOnce technology for deploying VSTO solutions.

The book covers well what we can do with VSTO but not how we should do it. If we fully understand this then the book will be a good resource for us. When covering several softwares the book cannot covering all details about VSTO for each software. However, it covers the more central parts and it does it well too.

With a page numbers of 1015 the book is thick as a brick and consider the content it should be treated as the “bible” for VSTO. I like the book for several reasons; it’s well written and it covers VSTO well. But again, if we do not know C# then the book will be of little value although it provides all examples in VB.NET (available as download from the book’s site).

Kind regards,

May 19, 2009

The 2nd Edition of Professional Excel Development (PED) is available!

Filed under: .NET Books, VSTO Books — Dennis M Wallentin @ 4:30 pm

The book “Professional Excel Development” (aka “PED”) is now available in the second edition. The major news is that .NET including VSTO has been added to the book. Because I’m responsible for the .NET section in the book the list of authors now also includes my name too.

2nd PED

For some weeks ago Ross McLean had the kindness to make an interview with me. Instead of repeating what I said in the interview you can read the full story here. However, I would like to thank Rob Bovey  for being an excellent teamleader for this edition. Another person I would also like to thank is Gabhan Barry (a Program Manager in the Excel group at Microsoft) who made the technical review on the .NET chapters.

New Chapters
The second editions includes five new chapters:

Chapter 10 – The Office 2007 Ribbon User Interface:
This chapter cover the Ribbon UI paradigm and discuss some advanced problem solving with the new UI.

Chapter 11 – Creating Cross-Version Applications:

In view of the fact that the Ribbon UI was introduced with Excel 2007 and that Windows Vista differ from Windows XP we decided to add this chapter. It covers how to create applications that target both Excel 2003 and previously versions and Excel 2007. We also discuss the major differences between the two operating systems.

Chapter 24 – Excel and VB.NET:
In the first part we introduce the IDE in detail and cover some basic VB.NET programming. In the second part of the chapter we discuss how to automate Excel with VB.NET.
The chapter present a practical case, PETRAS Report Tool.NET which is a standalone utility to retrieve data from PETRAS SQL Server database and populate some Excel reports templates.

Chapter 25 – Writing Managed COM Add-ins with VB.NET:
This chapter is the “flag ship” of the .NET section and goes in detail on how to create managed COM Add-ins and it also discuss in detail Automation Add-ins.
Here we also port the practical standalone utility, PETRAS Report Tool.NET. to a managed COM Add-in.

Chapter 26 – Developing Excel Solutions with Visual Studio Tools for Office System (VSTO):
In the first part of the chapter we set focus on two questions: What is VSTO and When to use VSTO. We also discuss VSTO add-ins, VSTO workbooks and how to deploy a VSTO workbook via the Web with the ClickOnce technology. To leverage the content in this chapter it requires that you have access to Excel 2007.

To buy the book or not?
If you have an interest in Excel and .NET including VSTO then you should consider buying the second edition. However, if your concern is native Excel and VBA then it is better that you save the money and wait until the next edition of the book is available.

If you want to buy or rank the book then please click here

PED’s site
With this edition we decided to build a completely new site for the book with Q&A forums. The intention with the site is to allow you to interact with the authors and leave suggestions for future editions of the book. The Q&A forums are built around the book’s content and they have a similar structure as the book’s TOC. In addition, it is here you will find information about updates and more downloads. 
We strongly encourage you to become a member and discuss the book in detail. Rob and I will try to answer all questions to the best of our knowledge. At the same time we feel it is important to state that the site is not a general Q&A forum about the involved technologies.

Together with the publisher we have made two chapters available for free download. To find out more please visit the PED’s site.

Let me know if you want any further information.

Kind regards,

January 20, 2008

RibbonX: Customizing the Office 2007 Ribbon – Review

Filed under: .NET Books, VSTO Books — Dennis M Wallentin @ 10:52 pm

RibbonX: Customizing the Office 2007 Ribbon
Robert Martin, Ken Puls, Teresa Henning (Oliver Stohr)
Target group:
According to me: Power user to professional VBA-developers
Targeting softwares in the Office suite:
Excel 2007, Word 2007 and Access 2007
7.5 (out of 10)

This book is the first of its kind as it explicit only target the RibbonX and how to manipulate and customize it. It covers how to work with RibbonX in Access, Excel and Word.

According to the book itself it contains two parts but for me it contains three parts where the first part serves as an introduction to RibbonX, XML and VBA. The second part is a walkthrough of the RibbonX’s object model and the final part leverage the preceding parts as well as cover some advanced topics.

Part I
The authors present the Ribbon UI and discuss its pros and cons. As we all know, new things solve some issues but also at the same time create new issues and the Ribbon UI is no exception from that rule. Two important tools are also presented, the Custom UI Editor and XML Notepad which is probably not so known among VBA-developers. Together with the presentation of the tools the tools’ pros and cons are also being discussed. ‘XML for RibbonX’ is also presented here together with a chapter for beginners of VBA.  

I find it to be good that the authors actually also discuss the shortcomings of new technologies and tools. The introduction is well written but does not give a ‘clear picture’ for the remaining parts of the book as it tries to target all groups from ‘novice to professional’.

Part II
This part is the backbone of the book as it describe the RibbonX’s objects model in detail and provides us with a great number of examples for Excel, Word and Access. Whenever there is a discrepancy between the softwares, on how they handle the RibbonX’s object model or how the XML files needs to be written, we are given examples that cover it. The presentations of the objects are strictly and a lot of data about the objects are given.

All the chapters are well written and it covers all aspects of the RibbonX’s objects model. It can be a little bit confusing when focus is switched from one software to another one but the authors have managed to keep it together.

Make sure You got the book available when developing RibbonX’s solutions as You need to go back to this part on a regular basis.

Part III
In this part the authors put things together and provide us with practical cases as well with some advanced topics on both VBA and on how to customize the RibbonX.

Here You will find some interesting aspects such as creating UI for Web Service and to work with contextual controls, keytips & keyboard shortcuts as well as how to share and deploy ribbon customizations. By the way, the chapter ‘Sharing and Deploying Ribbon Customizations’ is excellent.

For me this part of the book is the best part. In these chapters the authors push the customization of the RibbonX to the limit but also at the same point out some ‘traps’ to avoid.

Missing entries
The following aspects would have been nice to take part of:

I consider the RibbonX UI design to be very important. Therefore I was surprised that the book didn’t cover or at least discussed best practice for Ribbon UI design.

The book discusses reading and writing settings to Windows registry which is good. The way security is implemented in Windows Vista that approach may not always be the best alternative. On the .NET platform we use XML files for various tasks and since RibbonX rely on XML it would have been natural to also cover XML files to store settings in.

A book that explicit focus on how to customize the RibbonX should at least have some part that introduce RibbonX developing on the .NET platform. It becomes more important when it exist some shortcomings via VBA which the book also discuss.

Special kudos to the authors for:

  • Emphasizing on functionality and by doing so they don’t get ‘carried away’. 
  • Recommend to use a table-driven approach when customizing the RibbonX.
  • RibbonX Naming Convention which is based on RVAB naming conventions. This is a good start in order to create a standard on how we name the RibbonX’s object model in code. We may not necessary agree but it allow us to discuss the subject.
  • The tool, imageMSO Reference, which allows us to lookup imageMSO and gets the XML code for the selected imageMSO. Of course, I would be very pleased if it had been created as a managed COM add-in but that’s another story. 

This book provides us with a depth on how to control the RibbonX and it does it well. 

If You are a serious VBA developer who target Access 2007 /  Excel 2007 / Word 2007 then this book is a must have.

Kind regards,

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,

Blog at WordPress.com.