Steve Hansen : Excel

Google Docs + Microsoft Excel = poorman's Excel Services (kinda, sorta)

hansen — Thu, 04 Oct 2007 01:42:00 GMT

Take one cup Google Docs, one cup Microsoft Excel and add a dash of programming. Observe.

Excel 2007 Calculation Bug

hansen — Wed, 26 Sep 2007 16:35:00 GMT

Question: When does 850 * 77.1 not equal 65,535?

Answer: When you're using Excel 2007.

A bug in Excel 2007 (and Excel Services) is causing issues around calculated values of 65,535 and 65,536. Calculations that evaluate to the values may incorrectly display 100,000 as the result (although internally, Excel stores the value as the correct result). Microsoft is officially calling the bug a "display" bug (see the link to David Gainer below) rather than a calculation bug. To the average end-user, it doesn't really matter what you call it because this boils down to a trust issue. As in, I trust that Excel always shows the correct results of calculations. The "don't use it until after SP1" crowd is collectively saying "I told you so".

This bug does not affect prior versions of Excel.

Like Simon, I'm a little surprised this issue hasn't gotten more attention. I thought I'd wake up today and see it on eWeek, Seattle P-I, etc. I guess everyone is still busy playing Halo 3. Anyway, here are some good sources of information on this issue:

Simon Murphy has a few posts in addition to the link earlier, see bug announcement and bug test code,
David Gainer (Microsoft) explains the bug.
Slashdot
Excel Newsgroup

The Excel Publish menu and Excel Services

hansen — Tue, 22 May 2007 15:40:00 GMT

I was more than a little bummed yesterday (annoyed is more like it) when I found out why I was missing the Excel Services option on the Publish menu in Excel.

Excel 2007 Enterprise Publish Menu

Little did I realize that all of the work I’ve been doing to date inside a Virtual PC image used Office Enterprise edition. I didn’t even think twice when I installed Office Professional (as I have for the past 10 years) on a new computer. But sure enough, one of the little nuggets missing from the Professional SKU is the essential (if you’re using Excel Services anyway) Excel Services item on the publish menu.

Excel 2007 Professional Publish Menu

So what’s the big deal with this menu item?
If you don’t have this menu item, you can still save a workbook to a document library (although it is not entirely intuitive how to do this) and view the workbook in Excel Services. What you miss out on is the Excel Services Options. The Excel Services options allow you to specify which items in a worksheet to publish. You can publish the whole workbook, individual worksheets, named ranges, and charts.

Additionally, you can specify workbook parameters using the Parameters tab. Parameters allow you to provide some interactive capability when viewing the workbook via Excel Services. For example, say you have a sales report and you want to allow people the ability to choose a sales region and a fiscal year. Given these two parameters, the report updates itself automatically. To do this, you name the ranges representing the region and the fiscal year and then use the Parameters tab to add these named ranges as workbook parameters.

Now, instead viewing a static workbook via a browser (which is cool and useful), people can view the workbook and interact with it (which is cool and useful squared). For example, opening the sales report in Excel Services looks like this (please excuse the simplistic and boring formatting):

In the next screen shot, I have entered “South” in the Region parameter and clicked Apply.

Hopefully the corporations out there that have been installing the Professional SKU since Office 97 don’t make the same mistake I did only to find out later that some of the great SharePoint integration that myself and others tout isn’t available in this SKU. Not having the Excel Services item on the publish menu significantly constrains the usefulness of Excel Services from an end-user perspective.

A comparison of the Office 2007 SKUs is found here. The Excel Services option on the Publish menu is considered part of the Integrated Enterprise Content Management functionality.

As a final thought, I find it interesting that Microsoft is choosing to bundle all of the interesting web related functionality only in its highest priced SKUs while competitors are rapidly building out modest priced entirely web-based Office Suites (granted they have a ways to go). It reminds me of the days when Microsoft was an up and comer against established products only now Microsoft is the established product and no longer the up and comer. If there is one thing Microsoft’s history has shown it’s that the risk to products is from the bottom end of the market, not the top. I’ll reiterate my former plea – add Excel Services functionality to Office Live and while you’re at it – please add the Excel Services item to the Publish menu of every version of Microsoft Office.

VSTO "Orcas" Sales Forecasting Demo at Financial Services Developer Conference

hansen — Tue, 01 May 2007 16:33:00 GMT

I spent Thursday, April 26th in New York attending the 5th Annual Financial Services Developer Conference. I was on hand to assist with one of the VSTO demonstrations. The demo used an application that OZ developed last year for the Office System Developers Conference. It is a sales forecasting application that was inspired by an honest to goodness real-world application.

As I looked through the agenda for this conference, one of the things that struck me was how grandiose many of the topics sounded. For example, one of the sessions is “All Nines. All the Time: Citigroup Apply & Buy Application.” This session discussed achieving five nines reliability. Now, don’t get me wrong, I truly appreciate the amount of engineering and effort required to build mission critical applications. What I’d like to see more of at these types of events, however, is more attention to the fundamentals. That is where the sales forecasting application demo fits in.

You see, I’d bet this Dell XPS M1710 that I can find the inefficient end-user pattern that I’m about to describe in every organization of significant size. The basic problem is the collection and consolidation of business data – particularly forecasting information. While the situation the demo tackles is sales forecasting, if you abstract one level, you’d find this same pattern everywhere.

For the millions of dollars corporations spend on enterprise software from gigantic ISV’s and the millions more spent on enterprise development, how come there is still so much low hanging fruit in the enterprise? Considering the amount of low hanging fruit that is ripe for collection, I'm continually amazed how quickly some folks can dismiss Office development so quickly. The sales forecasting demo that we built can be built by any Office developer extremely quickly.

In the sales forecasting scenario, you have sales manager(s) and sales people. The sales people need to forecast their sales over the year. The manager(s) need to review individual sales forecasts and consolidated forecasts. Depending on the manager(s) level in the organization, they may need to see consolidated forecasts at multiple levels of the organization.

The end-user solution to this problem is to create an Excel workbook and share (via email or a shared-server) it with all of the sales people. The sales people fill out their forecasts and then email it to someone or save it to a prescribed server location.

Now, some poor soul, usually a financial analyst, has to consolidate all or the workbooks. Usually this is performed by creating an elaborate series of linked workbooks. In order to handle each time period, the file system is used and the series of workbooks are copied into new folders. The end result is a whole bunch of files like the example shown below.

FIGURE 1: A TYPICAL END-USER SOLUTION TO FILE CONSOLIDATION

I probably don’t need to tell you how time-consuming this process is. Nor do I need to tell you how fragile and error-prone it can be. I know – I used to be a financial analyst. I’ve lived it. We used to refer to this as the equivalent of burger flipping for MBA’s. Given the price of MBA's today, this is a valuable problem to solve.

The generic solution to this problem is to find a way to store all of the data in a database and then provide a way for users to input and modify data appropriately. If we agree that the data should be stored in a central database, then the only problem left to solve is how to allow the end-user to interact with the data. For all practical purposes, the interaction choices are: web-based or smart-client based. The sales forecasting demo takes the smart-client based approach, using the VSTO ServerDocument class to provide both web-based dashboard integration as well as an offline experience.

FIGURE 2: AN EXCEL SALES FORECASTING APPLICATION DEVELOPED WITH VSTO "ORCAS"

Figure 2 shows a screen shot of the sales forecasting demo. We originally developed it using VSTO 2005 and targeting Excel 2003. Ryan migrated it to VSTO "Orcas" and Excel 2007 in preparation for the Financial Services Developer Conference last week. This demo really deserves an entire blog post or article to explain all of the capabilities we built into it - being inspired by a "real-world" application, it is fairly comprehensive. Basically, it consists of two worksheets a dashboard and the forecast. The dashboard is shown in the screenshot. The forecast worksheet contains the monthly details.

The demo app uses role based security. Sales managers can navigate throughout the organizational hierarchy displayed in the task pane. As you select items in the org hierarchy, the dashboard and forecast worksheets update accordingly. A sales person can only view/edit their own data - the hierarchy is disabled.

Pretty standard fare technically, but extremely valuable versus the ad-hoc scenario that is abundant in corporate America today. Anyone feel like picking some fruit?

Excel Find method example using C#/VSTO

hansen — Thu, 29 Mar 2007 16:14:00 GMT

I've always found Excel's Find/FindNext methods awkward to use. It just seemed to require so many lines of code versus other common operations. I guess I've always wanted a simple FindAll() method that would find everything at once and give me a range containing all of the cells that met my search condition.

Anyway, the purpose of this post is just to share a basic C# example of how to use Find and FindNext to search through all the cells on a worksheet.

private void FindExample(Excel.Worksheet ws, string sFindMe)
{
Excel.Range rgFound;
string sFirstFoundAddress;

    // Find's parameters are "sticky". If you don't specify them
    // they'll default to the last used values - including parameters
    // set via Excel's user interface
    rgFound = ws.Cells.Find(sFindMe,
                            ws.Cells[1, 1],
                            Excel.XlFindLookIn.xlValues,
                            Excel.XlLookAt.xlPart,
                            missing,
                            Excel.XlSearchDirection.xlNext,
                            false,
                            missing,
                            missing);

    // If Find doesn't find anything, rgFound will be null
    if(rgFound != null)
    {
        // Save the address of the first found item -
        // it will be used in a loop terminating condition.
        sFirstFoundAddress = rgFound.get_Address(
                true, true, Excel.XlReferenceStyle.xlA1, missing, missing);

MessageBox.Show("Found match at cell " + sFirstFoundAddress);

        // Continue finding subsequent items using FindNext
        rgFound = ws.Cells.FindNext(rgFound);
        string sAddress = rgFound.get_Address(
                true, true, Excel.XlReferenceStyle.xlA1, missing, missing);

        // Start a loop that calls FindNext until
        // the first found cell is found again
        while (!sAddress.Equals(sFirstFoundAddress))
        {
            MessageBox.Show("Found match at cell " + sAddress);
            rgFound = ws.Cells.FindNext(rgFound);
            sAddress = rgFound.get_Address(
                true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
        }

}
MessageBox.Show("The worksheet has been searched.");
}