Deprecated: Assigning the return value of new by reference is deprecated in /nfs/c02/h13/mnt/25679/domains/vstotips.com/html/wp-includes/cache.php on line 36

Deprecated: Assigning the return value of new by reference is deprecated in /nfs/c02/h13/mnt/25679/domains/vstotips.com/html/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /nfs/c02/h13/mnt/25679/domains/vstotips.com/html/wp-includes/theme.php on line 507
VSTO Tips
Oct 03

Simon and I have been having some similar thoughts regarding hooking Google Docs up to a client side app. While he has been looking at an OpenOffice/Google Docs combo, I’ve been thinking about a Microsoft Excel/Google Docs combo. 

I have always been a supporter of Excel Services. I think that Excel Services has lots of potential. That said, my biggest beef with Excel Services is that it isn’t widely available. No MOSS, no Excel Services. Recently I’ve been experimenting with Google Docs and it became very clear to me that some of Excel Services potential (in regards to scenarios that I’ve been envisioning) is already available by combining Excel with Google Docs. Furthermore, since Google Docs doesn’t require a substantial investment in server software, it is approachable by a much broader audience than Excel Services.

The scenario I’ve been thinking about is basically a sales forecasting application for a manufacturing company. The manufacturing company goes through an annual planning process that involves collecting sales forecasts from dozens of independent distributors. Then the forecasts need to be consolidated and analyzed. Like anything in IT, there are several different ways you can go about facilitating this process. One of the constraints in this case is that the manufacturer has no control over the software used by the distributors.

My goal was to see how easy this type of scenario could be addressed using Google Docs in conjunction with Excel. In a nutshell, I found that a basic solution could be implemented very easily that addresses the needs of this scenario without requiring much in terms of specialized help. Here is what I did:

  1. Setup a Google Docs account
  2. Download the Google Data API
  3. Create a Google Docs sales forecast template (one for each distributor)
  4. Create an Excel 2007 Add-in project using VSTO 2005 SE (once released, it’d probably make more sense to make this a doc-level solution using VSTO 3.0 with Visual Studio 2008).
  5. Use the Google Docs API to retrieve data into an Excel workbook used for consolidation.


First impression? Favorable. I’m going to keep digging. I’ll post more of the technical details in a follow-up post.

Sep 27

Well, here it is. First post. If you’ve followed my first blog, you already know that I’m a bit of an Office Zealot. Truth of the matter is that I’ve always been more of an Excel zealot rather than an Office zealot. Yeah - I can get around the Outlook object model; sure - Word is okay; yep - SharePoint is the future. Frankly, I’ve never been that enthused about anything else besides Excel.

Why is that? I’d have to say Excel fits my personality. I enjoy analyzing things as evidenced by acquiring an MBA with a concentration in finance, working as a financial analyst for several years (and analyst starts with anal you know), and making a living the past decade as a consultant analyzing other peoples problems and developing systems to help solve them. Excel is very natural to me (except for this bug that is - I find it very unnatural).

OK, so where am I going with this. Oh yeah - while this is a VSTO blog - it will be more of an Excel VSTO blog, occassionally litered with Outlook, Word, and SharePoint posts. Also this blog will be as much about .NET Excel development as it will be about VSTO.

To clarify this statement requires a little background in to just what VSTO is and how it works. In a nutshell VSTO is a toolset that allows you to automate Excel using managed .NET code. Excel however is a COM based (un-managed) application. When it comes to Excel development, .NET and COM seem to go together like a peanut butter and pickle sandwhich. If that were the end of things that’d be a big enough hurdle to worry about. But it gets weirder…

You see, in order to have a COM based application work with .NET code, you need a layer of software in between the two called an interop. The Office team at Microsoft is responsible for creating the primary interop assembly (PIA) for Excel. The VSTO team at Microsoft reports up through an entirely different division at Microsoft - the Visual Studio team. The problem with this arrangement is that VSTO is entirely dependant on the Excel PIA. Since VSTO isn’t in the same line of reporting, they don’t explicitly have any power to affect change on the Office side of the fence. How this affects you is that as someone trying to learn how to use VSTO, much of the frustation you’ll encounter is due to the oddities of the Excel PIA, rather than functionality provided by VSTO.

Further compounding this issue is that these PIAs aren’t documented sufficiently and that as much as the VSTO team would like you to use VSTO, they are a bit powerless to document the PIAs (the “not my problem” and/or “not my budget” syndrome). If you post problems to the VSTO forum you’ll occasionally run up against this line of thought. Anyway, to wrap this point up, as far as this blog is concerned PIAs are “covered” under my definition of VSTO.