.NET4Office

VB Code Samples for the VSTO for Office 2007 Book Now Available

2009-11-18T13:24:00Z

I wanted to make sure everyone knows that VB code samples for the VSTO for Office 2007 book are now available.

You can download them here:

http://www.informit.com/content/images/9780321533210/downloads/VB%20Code%20Listings.zip

Long live VB!

8. VSTO Bug Tracker : Using a Ribbon

2009-05-14T21:19:20Z

To get started, from the Project menu choose Add New Item. In the Add New Item dialog box, click Ribbon (Visual Designer). Then click the Add button. A new Ribbon is created and the Ribbon Designer is displayed.

Next, click on the Group control that is created for you and change the Label in the Properties window to “Bugs” as well. Drag and drop a Button control from the Toolbox onto the Group control. Use the Properties window to set the Label for the button to “Add Bug Data”. Also, set the ControlSize to RibbonControlSizeLarge and set an Image for the button if you like. Double click on the Button control to add an event handler. The event handler will invoke the PopulateSpreadsheet method. You can now remove the SheetBeforeRightClick handler that we added earlier if you like.

    Private Sub AddBugData_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles AddBugData.Click
        Globals.ThisAddIn.PopulateSpreadsheet(True)
    End Sub

Then, drag and drop a Gallery control from the Toolbox onto the Group control. Use the Properties window to set the Label for the Gallery control to “Bug Charts”. Also, set the ControlSize to RibbonControlSizeLarge and set an Image for the Gallery control if you like.

With the Gallery control configured the way we want, let’s write some code behind it. Click on the Gallery control again to select it. Then click on the lightning bolt icon button in the Properties window to show the events for the Gallery control. Three events are displayed. Click is raised when an item in our Items collection is clicked. And ItemsLoading is raised when the user drops down the Gallery control before its contents are displayed—this is the event that can be handled at runtime to modify the Items and Buttons lists before the Gallery control is displayed to the user.

We want to add handlers for ItemsLoading and Click. First, let’s write the handler for the ItemsLoading event. Double click on the ItemsLoading event in the Properties window and Visual Studio generates an event handler in the code behind the Ribbon and displays the event handler. Add the code below. What does this code do? It will dynamically populate the Gallery control with a RibbonDropDownItem for each chart in the workbook. It sets the RibbonDropDownItem to the image of the chart. It also tags each RibbonDropDownItem with an identifier that the Click event will use.

    Private Sub Gallery1_ItemsLoading(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Gallery1.ItemsLoading
        Gallery1.Items.Clear()

        Dim c As Object
        Dim s As Excel.Worksheet
        Dim tag As Integer

        Dim tempFileName As String = System.IO.Path.GetTempFileName()

        For Each s In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
            Dim i As Integer
            For i = 1 To s.ChartObjects.Count
                Dim d As New RibbonDropDownItem
                c = s.ChartObjects(i)

                c.Chart.Export(tempFileName, "BMP")
                Dim bmpTemp As New System.Drawing.Bitmap(tempFileName)
                Dim bmpUnlinked As New System.Drawing.Bitmap(bmpTemp)
                bmpTemp.Dispose()
                d.Image = bmpUnlinked

                d.Tag = tag
                Gallery1.Items.Add(d)
                tag = tag + 1
            Next
        Next

        System.IO.File.Delete(tempFileName)
    End Sub

Now click on the Gallery control again to select it, then double click on the Click event in the Properties window and Visual Studio generates an event handler in the code behind the Ribbon and displays the event handler. In the Click event we will activate the chart corresponding to the RibbonDropDownItem.

    Private Sub Gallery1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Gallery1.Click
        Dim tagFound As Integer = Gallery1.SelectedItem.Tag
        Dim s As Excel.Worksheet
        Dim tag As Integer

        For Each s In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
            Dim i As Integer
            For i = 1 To s.ChartObjects.Count
                If tagFound = tag Then
                    s.ChartObjects(i).Activate()
                    Exit Sub
                End If
                tag = tag + 1
            Next
        Next
    End Sub

When we run the add-in, we get this result when you drop down the gallery control for a document with two charts in it. The gallery control updates to display images of the current charts in the workbook. If you click one of the drop down items, it activates the corresponding chart in the workbook.

That’s all folks! Hoped you enjoyed this example of using VSTO.

7. VSTO Bug Tracker : Using Document-Level Features in Add-Ins

2009-05-14T21:18:11Z

Prior to Visual Studio 2008 SP1, you couldn’t use common VSTO features like controls in the document and databinding to list objects at the add-in level. But with SP1 we introduced a way for you to use these features at the add-in level. In this section some of the wierd ways Idid things in section 4 will start to make more sense—I designed the code in Section 4 to be easily portable to an add-in.

One thing that won’t port to the add-in level is cached data—that feature only works when code is at the document level.

Choose File > Add > New Project.. and create a new Excel add-in project. I’ll be using VB in this example.

Add a web reference to the BugService as described in Article 4. Be sure to name the reference BugService.

Now, double click on your ThisAddIn file. In the ThisAddIn class, add this as a member variable. We won’t be adding any our dataset as a member variable because we can’t cache it at the add-in level, so there is no reason to make it a class member variable.

    Public buttonDictionary As New Dictionary(Of Excel.Worksheet, Microsoft.Office.Tools.Excel.Controls.Button)

Next, we will put some calls to our AddListObjectAndButton function. Add an event handler for the NewWorkbook, WorkbookBeforeClose, and WorkbookOpen events. You will also put some code in ThisAddIn_Startup. Also, handle the SheetBeforeRightClick event temporarily until we add a ribbon in the next article—this will make it so that if you right click on a worksheet, the bug data will be added to the workbook.

    Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        If Application.ActiveWorkbook IsNot Nothing Then
            PopulateSpreadsheet(False)
        End If
    End Sub

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

    End Sub

    Private Sub Application_NewWorkbook(ByVal Wb As Excel.Workbook) Handles Application.NewWorkbook
        PopulateSpreadsheet(False)
    End Sub

    Private Sub Application_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Range, ByRef Cancel As Boolean) Handles Application.SheetBeforeRightClick
        PopulateSpreadsheet(True)
    End Sub

    Private Sub Application_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeClose
        CleanUpDynamicButton()
    End Sub

    Private Sub Application_WorkbookOpen(ByVal Wb As Excel.Workbook) Handles Application.WorkbookOpen
        PopulateSpreadsheet(False)
    End Sub

The rest of the code is almost identical to the code we wrote in Article 4. PopulateSpreadsheet has these changes from the document to the add-in level.

1) Instead of the line “vstoSheet = GetVstoObject(s)” it changes to “vstoSheet = s.GetVstoObject()”. At the add-in level, GetVstoObject is an extension method to the Microsoft.Office.Interop.Excel.Worksheet type.

2) We don’t have the “If NeedsFill(“bugDataSet”)” if clause—cached data isn’t supported at the add-in level.

3) Since bugDataset isn’t needed to be declared at the class member level due to no cached data, we declare it local in PopulateSpreadsheet. Also you might need to add an “Imports System.Data" to the top of the file.

4) We’ll also add a line at the top of PopulateSpreadsheet to detect if the name of the active workbook is “BugWorkbook”—the wokrbook we created in Section 4. If it is, we’ll immediately return so as not to have our add-in conflict with our document level solution.

Other than these four changes, the remaining code should look very familiar:

  Public Sub PopulateSpreadsheet(ByVal alwaysAdd As Boolean)
        If Application.ActiveWorkbook.Name = "BugWorkbook" Then Exit Sub

        Dim foundList As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing
        Dim bugDataset As DataSet


        foundList = FindList()

        ' If not there create it if always add is true
        If foundList Is Nothing Then
            If alwaysAdd = True Then
                s = Application.ActiveSheet
                foundList = s.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, s.Range("A1", "D2"))
                foundList.Name = "BugListObject"
            Else
                Exit Sub
            End If
        Else
            s = TryCast(foundList.Parent, Excel.Worksheet)
        End If

        ' Dynamically create the list object
        Dim vstoSheet As Microsoft.Office.Tools.Excel.Worksheet
        vstoSheet = s.GetVstoObject()

        ' Do we already have a listobject, if so exit.
        If vstoSheet.Controls.Contains("BugListObject") Then
            Exit Sub
        End If

        Dim list As Microsoft.Office.Tools.Excel.ListObject = vstoSheet.Controls.AddListObject(foundList)

        'If NeedsFill("bugDataset") Then
        Application.StatusBar = "Updating data from bug web service..."
        Dim bugService As BugService.BugService = New BugService.BugService()
        bugDataset = bugService.BulkDataExport("", "")
        'End If

        list.AutoSetDataBoundColumnHeaders = True
        list.DataSource = bugDataset.Tables(0)
        list.Range.Columns.AutoFit()
        list.Disconnect()

        Dim buttonRange As Excel.Range = s.Range("E1")
        Dim pivotButton As Microsoft.Office.Tools.Excel.Controls.Button
        pivotButton = vstoSheet.Controls.AddButton(buttonRange, "CreatePivotTable")
        buttonDictionary.Add(s, pivotButton)
        AddHandler pivotButton.Click, AddressOf pivotButton_Click
        pivotButton.Text = "Pivot"

        Application.StatusBar = ""
        s.Name = "Bug Data"
        s.Activate()
    End Sub

    Private Function FindList() As Excel.ListObject
        ' Do we already have a created list object?
        Dim l As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing
        For Each s In Application.ActiveWorkbook.Worksheets
            For Each l In s.ListObjects
                If l.Name = "BugListObject" Then
                    Return l
                End If
            Next
        Next
        Return Nothing
    End Function

    Private Sub pivotButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim s As Excel.Worksheet = Nothing
        Dim pivotCache As Excel.PivotCache
        Dim pivotTables As Excel.PivotTables
        Dim pivotTable As Excel.PivotTable
        Dim pivotField As Excel.PivotField

        s = Application.ActiveWorkbook.Worksheets.Add()
        pivotCache = s.Parent.PivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, FindList().Range, Excel.XlPivotTableVersionList.xlPivotTableVersion12)
        pivotTables = s.PivotTables()
        pivotTable = pivotTables.Add(pivotCache, s.Range("A1"))

        pivotTable.AddDataField(pivotTable.PivotFields(4))
        pivotTable.AddFields("Date")

        pivotField = pivotTable.PivotFields("Column")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        pivotField.CurrentPage = "Active"

        pivotField = pivotTable.PivotFields("Team")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        Dim project As String = InputBox("Enter the team", "Team name", "Project - Office Client")
        pivotField.CurrentPage = project

        s.Name = project
    End Sub

    Private Sub CleanUpDynamicButton()
        Try
            Dim l As Excel.ListObject = FindList()
            If (l Is Nothing) Then
                Exit Sub
            End If
            Dim vstoButton As Microsoft.Office.Tools.Excel.Controls.Button
            vstoButton = buttonDictionary(l.Parent)
            RemoveHandler vstoButton.Click, AddressOf pivotButton_Click
            buttonDictionary.Remove(l.Parent)
        Catch ex As Exception

        End Try
    End Sub

Now, when you run this add-in and right click on the worksheet, the bugs dataset will be added to the workbook along with the “Pivot” button.

Next up, we’ll enhance our solution with a custom ribbon.

6. VSTO Bug Tracker: Word Report Generation

2009-05-14T21:17:17Z

Another thing I want my bug tracking system to do is help me generate a weekly report with a summary table of key stats for all my teams. I want to do this in Word so I can add my own comments and a status summary of what went on during the week.

We already prepared to build this Word report by writing the Web Service method “GetDailyValues”. Create a new Word document project in C# and call it BugDocument. If you start from a new document, follow the instructions below. If you want to start from an existing document I’ve provided one here:

http://ericca.members.winisp.net/bugdocument.docx

Add a heading in the document called “Bug Report for “ then drag and drop a DatePickerContentControl in. Next, add a table with 6 rows and 5 columns. The headings for the columns are Active, Incoming, Fix Rate, and Resolve Rate. The headings for the rows are Office Client, Office Server, Pro Tools, VBA, and VSTA. Now drag and drop in new PlainTextContentControls left to right in this order into the table:

1 2 3 4

5 6 7 8

9 10 11 12

13 14 15 16

17 18 19 20

This results in the document shown below:

Next, add a WebService reference to our BugService as described in Article 4. Make sure the WebService is named BugService.

We’re going to add an event handler for the DatePickerContentControl we added to the heading. Click on the DatePickerControl, then use the Properties window to add an event handler for the Exiting event.

In the code behind for ThisDocument, add a member variable to hold the dataset we will get back from the web service.

        public DataSet ds;

The exiting event should look like this. It gets the date that is picked, calls the web service, and calls the BindControls (if this is the first call) which establishes databindings between all the content controls you added and the dataset.

    private void datePickerContentControl1_Exiting(object sender, Microsoft.Office.Tools.Word.ContentControlExitingEventArgs e)
    {
        DateTime date = System.Convert.ToDateTime(datePickerContentControl1.Text);
        BugService.BugService bugService = new BugService.BugService();

        if (ds != null)
        {
            ds.Clear();
            DataSet d = bugService.GetDailyValues(date.Date.ToShortDateString());
            ds.Merge(d);
        }
        else
        {
            ds = bugService.GetDailyValues(date.Date.ToShortDateString());
            BindControls();
        }
    }

    private void BindControls()
    {
        this.plainTextContentControl1.DataBindings.Add("Text", ds, "Table.1");
        this.plainTextContentControl2.DataBindings.Add("Text", ds, "Table.2");
        this.plainTextContentControl3.DataBindings.Add("Text", ds, "Table.3");
        this.plainTextContentControl4.DataBindings.Add("Text", ds, "Table.4");

        this.plainTextContentControl5.DataBindings.Add("Text", ds, "Table.5");
        this.plainTextContentControl6.DataBindings.Add("Text", ds, "Table.6");
        this.plainTextContentControl7.DataBindings.Add("Text", ds, "Table.7");
        this.plainTextContentControl8.DataBindings.Add("Text", ds, "Table.8");

        this.plainTextContentControl9.DataBindings.Add("Text", ds, "Table.9");
        this.plainTextContentControl10.DataBindings.Add("Text", ds, "Table.10");
        this.plainTextContentControl11.DataBindings.Add("Text", ds, "Table.11");
        this.plainTextContentControl12.DataBindings.Add("Text", ds, "Table.12");

        this.plainTextContentControl13.DataBindings.Add("Text", ds, "Table.13");
        this.plainTextContentControl14.DataBindings.Add("Text", ds, "Table.14");
        this.plainTextContentControl15.DataBindings.Add("Text", ds, "Table.15");
        this.plainTextContentControl16.DataBindings.Add("Text", ds, "Table.16");

        this.plainTextContentControl17.DataBindings.Add("Text", ds, "Table.17");
        this.plainTextContentControl18.DataBindings.Add("Text", ds, "Table.18");
        this.plainTextContentControl19.DataBindings.Add("Text", ds, "Table.19");
        this.plainTextContentControl20.DataBindings.Add("Text", ds, "Table.20");
    }

When we run this solution, and pick a date from the date picker, the table is filled in from the data set returned by the web service showing current bug statistics for that date.

Although this is as far as we are going to take this part of the solution, you can imagine some additional things we could do. First, because we use databinding, we could update our web service to return more than a single row, and use a currency manager to move through the rows in the dataset which would cause all the bound controls to update automatically. This is a potential reason to use this technique (databinding) as opposed to the other technique you can use with content controls which is doing xpath bindings to an XML xpart embedded in the document.

Also, we could use cached data in this solution in a similar way as was explained in Article 5 to populate the dataset in the document without starting Word.

In the next section, I’m going to show you how we can take some of the functionality in my bug workbook to the add-in level so it will work against any spreadsheet without requiring any code in a spreadsheet.

5. VSTO Bug Tracker : Using Cached Data

2009-05-14T21:15:10Z

We are now going to convert our previous sample to use cached data. In our current solution we have code that when the document loads, it calls a web service to get the bug data. We are going to use the cached data feature to allow us to call the web service at a different time to fill in the dataset in the document without having to start Excel.

Why would we want to do this? Well, several reasons. First, we can improve the load time of our document by updating the data in the document in advance. This also is handy to update the data in the document if the document is going to be opened outside the firewall where the web service can’t be accessed. So a process running on a machine within the firewall can update the data in the document and then give the document to a machine running outside the firewall. As mentioned in the last article, it can also allow me to update the bug data in my workbooks in the middle of the night via a scheduled process so I can hit the web service when traffic is low.

To convert our previous solution to used cached data, we simply add the CachedData attribute to the DataSet we declared in Sheet1.

    <Cached()> Public bugDataset As DataSet

Next, we’ll modify our implementation of PopulateSpreadsheet. The change here is to uncomment out the if statement If NeedsFill("bugDataset") Then which now brackets the code that calls the web service to update bugDataset. What will happen now is that there are two ways of initializing the dataset in the document. If the document gets run immediately after it is built, bugDataSet will not yet be cached in the document, so the bugDataSet will be updated by the web service call. On subsequent runs after the document is saved, the web service won’t be called because the dataset will be cached in the document already. The second way that the dataset can be initialized is through a console app that we will write presently.

        If NeedsFill("bugDataset") Then
            Application.StatusBar = "Updating data from bug web service..."
            Dim bugService As BugWorkbook.BugService.BugService = New BugWorkbook.BugService.BugService()
            bugDataset = bugService.BulkDataExport("", "")
        End If

Now, lets create a console application that can populate the data set into the document without starting Excel. Use File > Add > New Project… to add a new console application to the solution (under Language, Windows, Console Application) and call it UpdateExcelWorkbook.

In this console application, we will be using a class called ServerDocument which lets us access the cached data without starting up Excel. Right click on the project node for the console application and choose “Add Reference…”. From the Add Reference dialog, locate the .NET assembly called Microsoft.VisualStudio.Tools.Applications.ServerDocument.v9.0 and add it as a reference.

Also add using Microsoft.VisualStudio.Tools.Applications; (Imports Microsoft.VisualStudio.Tools.Applications for VB) and using System.Data; (Imports System.Data) to the Program.cs (or Program.vb) file.

We must also add a web reference to BugService—follow the steps given in article 4, and be sure to name it BugService.

Now we’ll write the code for the console application. The code creates a new instance of the ServerDocument class, iterates over the cached data until it finds a cached dataset called “bugDataSet”—the name of the public variable we added to Sheet1. It then updates that dataset by calling the web service, then using a method called SerializeDataInstance which writes the dataset into the document. Finally it calls doc.Save then exits.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualStudio.Tools.Applications;
using System.Data;

namespace UpdateExcelWorkbook
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 1)
            {
                Console.WriteLine("Usage:");
                Console.WriteLine("   UpdateExcelWorkbook.exe myfile.xlsx");
                return;
            }

            string filename = args[0];
            ServerDocument doc = null;

            try
            {
                doc = new ServerDocument(filename);

                foreach (CachedDataHostItem view in
                  doc.CachedData.HostItems)
                {
                    foreach (CachedDataItem item in view.CachedData)
                    {
                        if (item.Id == "bugDataset")
                        {
                            BugService.BugService bugService = new BugService.BugService();
                            DataSet ds = bugService.BulkDataExport("", "");
                            item.SerializeDataInstance(ds);
                            doc.Save();
                            continue;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Unexpected Exception:" + filename);
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                if (doc != null)
                {
                    doc.Close();
                }
            }
        }
    }
}

Now, to make this run, go back to the BugWorkbook project node, right click on it, and choose “Open Folder in Windows Explorer”. We are trying to get the full path to the excel workbook that is being built by Visual Studio. Double click on the bin directory, then the debug directory, then copy the whole path to the clipboard. On my computer it is C:\Users\ecarter\Documents\Visual Studio 2008\Projects\BugService\BugWorkbook\bin\Debug\BugWorkbook.xlsx

Right click on the UpdateExcelWorkbook project node, and choose properties. Click the Debug tab. In that tab, paste into the Command line arguments text box the full path to the workbook as shown below.

Now, set the UpdateExcelWorkbook project to be the startup project by right clicking on the UpdateExcelWorkbook project node and choosing “Set as Startup Project”. Clean and rebuild the solution. This will rebuild the Excel workbook “ExcelWorkbook.xlsx” which will now have an empty data set in it called bugDataSet after the rebuild. After the console application runs, bugDataSet will have been populated into the document.

To verify that is so, navigate back to the directory where the excel workbook (now modified by the console application) lives (e.g. C:\Users\ecarter\Documents\Visual Studio 2008\Projects\BugService\BugWorkbook\bin\Debug\BugWorkbook.xlsx) and launch the workbook. As it loads, note that the Status Bar of Excel does not display the text “Updating data from web service” because the dataset is already in the document. When it starts up, it immediately binds the already populated dataset to the list object in the document.

4. VSTO Bug Tracker : Getting the data into Excel

2009-05-14T21:14:40Z

The next step is to get more of the bug data into Excel so we can start to analyze it. To do this, we will use VSTO’s data bind to ListObject support.

I’m going to approach this in a different “more advanced” way that will make sense later in the demo. Long time VSTO developer will know that the simple way to bind data to a list object in VSTO is to create an Excel Workbook project, add a data source, then drag and drop the data source onto the Excel Workbook.

The problem with that approach for this demo is that the code that is generated is deeply tied to the specific workbook being designed. Since I’m going to show some advanced tips and tricks here including using document features in an add-in, I’m going to do this in a way that doesn’t tie it to a specific workbook project.

We want to add the new Excel Workbook project to our existing solution that has our BugService project in it. To do this, select File > Add > New Project. Pick Visual Basic or Visual C# then Office then 2007 in the Project types outline. Then pick Excel 2007 Workbook project and name it BugWorkbook:

Select Create a new document in the next dialog box that appears then click OK.

Now we want to add a web reference to our BugService web service. Right click on the BugWorkbook project node in Solution Explorer and choose Add Service Reference... Then click on the Advanced button then the Add Web Reference.. button. Click the “Web Services in this Solution” link. Then click “BugService”. Change the web reference name from localhost to BugService and click the Add Reference button:

Now, right click on Sheet1.vb (or Sheet1.cs) and choose View Code.

Add these two member variable to the ThisWorkbook class. We need a variable to hold onto our dataset and one to hold onto a button we will be dynamically creating. The reason we are using a dictionary object to store our button will be apparent later in the demo, for this section it will feel like overkill.

    Public bugDataset As DataSet
    Public buttonDictionary As New Dictionary(Of Excel.Worksheet, Microsoft.Office.Tools.Excel.Controls.Button)

Startup and Shutdown for the sheet look like this:

    Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        PopulateSpreadsheet(True)
    End Sub

    Private Sub Sheet1_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown
        CleanUpDynamicButton()
    End Sub

The PopulateSpreadsheet method takes a boolean parameter that sets whether or not to force that the data be added. The reason for this parameter will be apparent later as well. This code finds the existing listobject with the bug data (if it has already been inserted into the spreadsheet in a previous run of the spreadsheet). If it hasn’t yet been created, it creates the list. It then contacts the web service (it does this every time the document opens), gets all the bug data, binds the resulting dataset to the list object, then it disconnects from the list object once it has been populated. Finally, it dynamically creates a button that if clicked will create a new sheet with a pivot table for the data in the list. It puts the button in a dictionary along with the worksheet it was created on so it can be cleaned up later (in CleanUpDynamicButton). The FindList method is a helper method that looks through the workbook to see if we’ve already added a listobject—it does this by looking for a listobject named “BugListObject”.

    Public Sub PopulateSpreadsheet(ByVal alwaysAdd As Boolean)
        Dim foundList As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing

        foundList = FindList()

        ' If not there create it if always add is true
        If foundList Is Nothing Then
            If alwaysAdd = True Then
                s = Application.ActiveSheet
                foundList = s.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, s.Range("A1", "D2"))
                foundList.Name = "BugListObject"
            Else
                Exit Sub
            End If
        Else
            s = TryCast(foundList.Parent, Excel.Worksheet)
        End If

        ' Dynamically create the list object
        Dim vstoSheet As Microsoft.Office.Tools.Excel.Worksheet
        vstoSheet = GetVstoObject(s)

        ' Do we already have a listobject, if so exit.
        If vstoSheet.Controls.Contains("BugListObject") Then
            Exit Sub
        End If

        Dim list As Microsoft.Office.Tools.Excel.ListObject = vstoSheet.Controls.AddListObject(foundList)

        'If NeedsFill("bugDataset") Then
        Application.StatusBar = "Updating data from bug web service..."
        Dim bugService As BugService.BugService = New BugService.BugService()
        bugDataset = bugService.BulkDataExport("", "")
        'End If

        list.AutoSetDataBoundColumnHeaders = True
        list.DataSource = bugDataset.Tables(0)
        list.Range.Columns.AutoFit()
        list.Disconnect()

        Dim buttonRange As Excel.Range = s.Range("E1")
        Dim pivotButton As Microsoft.Office.Tools.Excel.Controls.Button
        pivotButton = vstoSheet.Controls.AddButton(buttonRange, "CreatePivotTable")
        buttonDictionary.Add(s, pivotButton)
        AddHandler pivotButton.Click, AddressOf pivotButton_Click
        pivotButton.Text = "Pivot"

        Application.StatusBar = ""
        s.Name = "Bug Data"
        s.Activate()
    End Sub

    Private Function FindList() As Excel.ListObject
        ' Do we already have a created list object?
        Dim l As Excel.ListObject = Nothing
        Dim s As Excel.Worksheet = Nothing
        For Each s In Application.ActiveWorkbook.Worksheets
            For Each l In s.ListObjects
                If l.Name = "BugListObject" Then
                    Return l
                End If
            Next
        Next
        Return Nothing
    End Function

The callback function for the dynamically created button looks like this. It creates a new pivot table on a new worksheet that is bound to the list object we created. It creates two page fields to filter the data by team and column. Finally, it prompts for the team name and sets the team filter to that team name (and sets the name of the newly created worksheet to the team name). This is basic Excel code, but it took me a while to figure out the Pivot Table object model.

    Private Sub pivotButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim s As Excel.Worksheet = Nothing
        Dim pivotCache As Excel.PivotCache
        Dim pivotTables As Excel.PivotTables
        Dim pivotTable As Excel.PivotTable
        Dim pivotField As Excel.PivotField

        s = Application.ActiveWorkbook.Worksheets.Add()
        pivotCache = s.Parent.PivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, FindList().Range, Excel.XlPivotTableVersionList.xlPivotTableVersion12)
        pivotTables = s.PivotTables()
        pivotTable = pivotTables.Add(pivotCache, s.Range("A1"))

        pivotTable.AddDataField(pivotTable.PivotFields(4))
        pivotTable.AddFields("Date")

        pivotField = pivotTable.PivotFields("Column")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        pivotField.CurrentPage = "Active"

        pivotField = pivotTable.PivotFields("Team")
        pivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField
        Dim project As String = InputBox("Enter the team", "Team name", "Project - Office Client")
        pivotField.CurrentPage = project

        s.Name = project
    End Sub

Finally, the CleanUpDynamicButton method finds the dynamically created button in the dictionary and removes it and cleans up the event handler, etc. This is in a try catch block just in case a button wasn’t created and therefore isn’t found in the dictionary which will be the case later in this demo.

    Private Sub CleanUpDynamicButton()
        Try
            Dim l As Excel.ListObject = FindList()
            If (l Is Nothing) Then
                Exit Sub
            End If
            Dim vstoButton As Microsoft.Office.Tools.Excel.Controls.Button
            vstoButton = buttonDictionary(l.Parent)
            RemoveHandler vstoButton.Click, AddressOf pivotButton_Click
            buttonDictionary.Remove(l.Parent)
        Catch ex As Exception

        End Try
    End Sub

Now set the BugWorkbook as the active project. So what’s the result when we run this document? It adds a list object bound to the data set to the active worksheet along with a dynamically created button with the caption Pivot.

When you click on the Pivot button, a new worksheet is created with a pivot table bound to the list object on the Bug Data worksheet. You are prompted during this process to set the Team filter—here we se tit to “Project – Office Client”

From here, it is a simple matter to add a nice pivot chart as well. Click on the Options tab in the Pivot Tables tab group, then press the PivotChart button. I can quickly get a nice bug chart as shown below. Pretty, but I wish the bug count was going down!

If I save my workbook then reopen it, the hook up code will run, detect that there is already a bug list object in the workbook, and it will fetch fresh data from the web service, rebind to the bug list object to update it, and re-add the dynamic “Pivot” button on the Bug Data page. I can click the Pivot button as often as I want to add new worksheets with new pivots on the data—for example, looking at different teams or the same team and different columns.

Sometimes the internal Microsoft server that returns the bug data can be slow or unavailable. So rather than fetch fresh data from the web server every time you open the document, I’m going to show you how cached data can let you refresh the data in the document during off times—we’re going to write a console application that can update the data in the document without starting Excel. It therefore could be run nightly on all my bug workbooks to refresh their data (since I don’t really need bug data more often than nightly anyway).

3. VSTO Bug Tracker : A UDF

2009-05-14T21:13:17Z

Now that we have our web service, lets get started by writing a managed UDF that uses the web method “GetColumnValue”. In fact, the UDF will mirror this web method and make it so we can use it in Excel formulas.

First, add a new C# or VB class library project to your solution using File > Add > New Project. Name the project BugAddIn. Next, lets add a web reference to the BugService.

In your Class1.cs or Class1.vb file created for you in the new project, replace Class1 with the code shown below. Replace the GUID string in the listing with your own GUID by using Generate GUID in the Tools menu. In the Generate GUID dialog box, pick option 4, Registry Format. Then click the Copy button to put the new GUID string on the clipboard. Then click Exit to exit the Generate GUID tool. Finally, select the GUID string and replace it with your new GUID string. You’ll also have to remove the { } brackets that get copied as part of the GUID.

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Runtime.InteropServices
Imports Microsoft.Win32

Namespace BugAddIn
    ' Replace the Guid below with your own guid that
    ' you generate using Create GUID from the Tools menu
    <Guid("6C0C5BEC-9F01-4743-97D3-CB4342644CDB")> _
    <ClassInterface(ClassInterfaceType.AutoDual)> _
    <ComVisible(True)> _
    Public Class BugFunctions

        Public Sub New()
        End Sub

        Public Function GetColumnValue(ByVal team As String, ByVal column As String, ByVal dateGathered As DateTime) As Double
            Dim bugService As New BugService.BugService()
            Return Convert.ToDouble(bugService.GetColumnValue(team, column, dateGathered.[Date].ToShortDateString()))
        End Function

        <ComRegisterFunctionAttribute()> _
        Public Shared Sub RegisterFunction(ByVal type As Type)
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"))
            Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), True)
            key.SetValue("", System.Environment.SystemDirectory + "\mscoree.dll", RegistryValueKind.[String])
        End Sub

        <ComUnregisterFunctionAttribute()> _
        Public Shared Sub UnregisterFunction(ByVal type As Type)
            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), False)
        End Sub

        Private Shared Function GetSubKeyName(ByVal type As Type, ByVal subKeyName As String) As String
            Dim s As New System.Text.StringBuilder()

            s.Append("CLSID\{")
            s.Append(type.GUID.ToString().ToUpper())
            s.Append("}\")
            s.Append(subKeyName)

            Return s.ToString()
        End Function
    End Class
End Namespace

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace BugAddIn
{
    // Replace the Guid below with your own guid that
    // you generate using Create GUID from the Tools menu
    [Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public class BugFunctions
    {
        public BugFunctions()
        {

        }

        public double GetColumnValue(string team, string column, DateTime dateGathered)
        {
            BugAddIn.BugService.BugService bugService = new BugService.BugService();
            return Convert.ToDouble(bugService.GetColumnValue(team, column, dateGathered.Date.ToShortDateString()));
        }

        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(Type type)
        {
            Registry.ClassesRoot.CreateSubKey(
            GetSubKeyName(type, "Programmable"));
            RegistryKey key = Registry.ClassesRoot.OpenSubKey(
            GetSubKeyName(type, "InprocServer32"), true);
            key.SetValue("",
            System.Environment.SystemDirectory + @"\mscoree.dll",
            RegistryValueKind.String);
        }

        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(Type type)
        {
            Registry.ClassesRoot.DeleteSubKey(
            GetSubKeyName(type, "Programmable"), false);
        }

        private static string GetSubKeyName(Type type, string subKeyName)
        {
            System.Text.StringBuilder s =
            new System.Text.StringBuilder();

            s.Append(@"CLSID\{");
            s.Append(type.GUID.ToString().ToUpper());
            s.Append(@"}\");
            s.Append(subKeyName);

            return s.ToString();
        }
    }
}

With this code written (remember to replace the GUID in the listing with your own GUID that you generate using Generate GUID in the Tools menu), you need to configure the project to be registered for COM interop so Excel can see it. Go to the properties for the project by double-clicking the Properties node under the BugAddIn project in Solution Explorer. In the properties designer that appears, click the Build tab and check the check box that says Register for COM interop as shown below This will cause Visual Studio to register the assembly for COM interop when the project is built.

If you are running under Vista or later, you need to run Visual Studio as administrator since registering for COM interop requires administrative privileges. If you are not already running Visual Studio as administrator, save your project and exit Visual Studio. Then find the Visual Studio 2008 icon in the start menu, right click on it and choose Run as Administrator as shown in Figure 3-8. Now that Visual Studio is running as administrator, reopen your project and choose Rebuild Solution from the Build menu. Visual Studio will do the necessary registration to make your class visible to Excel.

Now that the add-in is built and registered, to load the managed automation add-in into Excel, follow these steps.

1. Launch Excel and click the Microsoft Office button in the top left corner of the window.

2. Choose Excel Options.

3. Click the Add-Ins tab in the Excel Options dialog.

4. Choose Excel Add-Ins from the combo box labeled Manage. Then click the Go button.

5. Click the Automation button in the Add-Ins dialog.

6. Look through the list of Automation Servers and find the class you created—it will be listed as BugAddIn.MyFunctions.

By clicking OK in this dialog, you have added the BugAddIn.MyFunctions class to the list of installed automation add-ins.

Now, try to use the function GetColumnValue in an Excel formula. Click an empty cell in the workbook, and then click the Insert Function button (the button with the “fx” label) in the formula bar. From the dialog of available formulas, drop down the “Or select a category” drop-down box and choose BugAddIn.MyFunctions. Then click the GetColumnValue function. When you click the OK button, Excel pops up a dialog to help select function arguments from cells in the spreadsheet.

After you have selected function arguments, click OK.

Note that Excel and .NET have some special issues when running on a non-english locale that may cause you to see an automation add-in to fail. VSTO add-ins have some additional features that protect you from these issues.

Also note that to uninstall your add-in, run regasm BugAddIn.dll /unregister at an eleveated command prompt.

2. VSTO Bug Tracker : The Web Service

2009-05-14T21:11:52Z

Within Microsoft, there is a web service already implemented that I can use in my solution to call the bug tracking system and get back the data described in the previous article. For my demo, I’m going to implement a demo web service that will stand in for that internal Microsoft web service. It will return the same basic data structures as the internal web service does, so when I run my own copy of VSTO bug tracker, I can swap out this demo web service for the real one.

The demo web service will have three web methods. The first method is called BulkDataExport. This method takes a start date and an end date and returns back a dataset with all the bug data for that time period. This is a simplified version of the Microsoft provided web service that I use internally. That web method has more parameters to specify milestone, release, team, etc. But I will omit those for simplicity since my data is specific to my team.

<WebMethod()> _
Public Function BulkDataExport(ByVal dateFrom As String, _
  ByVal dateTo As String) As System.Data.DataSet

[WebMethod()]
public System.Data.DataSet BulkDataExport(string dateFrom, string dateTo)

The second method is called to get one particular bug stat value for one particular date. This will be used by the UDF I will write later. This takes a particular team (milestone and release I omit) as well as the name of the column I want to retrieve (e.g. “Active”) and the date for which I want to get the value. So I can effectively ask the question through this web method, “How many active bugs did the VBA team have on May 1st?”

<WebMethod()> _
Public Function GetColumnValue(ByVal team As String, _
  ByVal column As String, ByVal dateGathered As String) As String

[WebMethod()]
public string GetColumnValue(string team, string column, string dateGathered)

The third method will be used by my Word solution. It takes the date and returns a dataset with twenty columns representing the 4 key metrics for my 5 teams:

<WebMethod()> _
Public Function GetDailyValues(ByVal dateGathered As String) As DataSet

[WebMethod()]
public DataSet GetDailyValues(string dateGathered)

Let’s walk through creating this web service.

Fire up Visual Studio 2008, select File > New > Project…, then expand out the Visual Basic or C# node, click the Web category, then pick ASP.NET Web Service Application. Name the project BugService, then click OK.

Now, let’s implement these two methods. First, let’s get a web service created. Delete the auto-created Service1.asmx file from your project. Right click on the project node, choose Add > New Item… and from the Add New Item dialog, click on the Web category, then click Web Service. Name the Web Service BugService.asmx. Then click Add.

Delete the “HelloWorld” web method that is created for you in the BugService.asmx file. Add a “using System.Data;” statement to the top of the file. Then add a DataSet property to the BugService class that will contain our data.

    Shared ds As New DataSet
    Public Shared ReadOnly Property BugData() As DataSet
        Get
            If ds.Tables.Count = 0 Then
                ds.ReadXml("C:\Users\ecarter\Documents\bugdata.xml")
            End If
            Return ds
        End Get
    End Property

    static DataSet ds = new DataSet();
    public static DataSet BugData
    {
        get
        {
            if (ds.Tables.Count == 0)
            {
                ds.ReadXml("C:\\Users\\ecarter\\Documents\\bugdata.xml");
            }
            return ds;
        }
    }

As you can see, this code kind of cheats—it loads a pre-serialized data set from disk. The data in this file has data that I originally obtained from Microsoft’s internal bug system. For my demo, I won’t live connect to this system, so instead I’ve saved off some XML. I also keep this around in a class level property so that I only have to load it once and subsequent web service calls that access this data won’t have to load all the bug data again and will therefore be quicker.

I’ve provided a copy of this data so you can play along here: http://ericca.members.winisp.net/bugdata.xml

Now, my implementation of the BulkDataExport web method. This method is pretty simple. It just grabs the data set with all the bug data and then does a restrictive query to only return the bug data between the dateFrom and dateTo. There’s some wierdness going on here with converting dates to strings and so forth—this is because I need to match the original Microsoft internal web service which has everything typed as string. For C#, be sure you add a using System.Data to the top of the class file.

    <WebMethod()> _
    Public Function BulkDataExport(ByVal dateFrom As String, ByVal dateTo As String) As System.Data.DataSet
        Dim rows As DataTable = BugData.Tables("Table")

        Dim startDate As DateTime
        If (String.IsNullOrEmpty(dateFrom)) Then
            startDate = Date.MinValue
        Else
            startDate = System.Convert.ToDateTime(dateFrom)
        End If

        Dim endDate As DateTime
        If (String.IsNullOrEmpty(dateTo)) Then
            endDate = Date.MaxValue
        Else
            endDate = System.Convert.ToDateTime(dateTo)
        End If

        Dim query = From row In rows.AsEnumerable() _
                    Where System.Convert.ToDateTime(row.Field(Of String)("Date")).Date >= startDate.Date And _
                    System.Convert.ToDateTime(row.Field(Of String)("Date")).Date <= endDate.Date _
                    Select row

        Dim newrows As DataTable = query.CopyToDataTable()
        newrows.TableName = "Table"

        Dim returnDataSet As New DataSet()
        returnDataSet.Tables.Add(newrows)
        Return returnDataSet
    End Function

    [WebMethod()]
    public System.Data.DataSet BulkDataExport(string dateFrom, string dateTo)
    {
        DataTable rows = BugData.Tables["Table"];

        DateTime startDate;
        if ((string.IsNullOrEmpty(dateFrom)))
        {
            startDate = System.DateTime.MinValue;
        }
        else
        {
            startDate = System.Convert.ToDateTime(dateFrom);
        }

        DateTime endDate;
        if ((string.IsNullOrEmpty(dateTo)))
        {
            endDate = System.DateTime.MaxValue;
        }
        else
        {
            endDate = System.Convert.ToDateTime(dateTo);
        }

        var query = from row in rows.AsEnumerable()
                    where System.Convert.ToDateTime(row.Field<string>("Date")).Date >= startDate.Date &&
                    System.Convert.ToDateTime(row.Field<string>("Date")).Date <= endDate.Date
                    select row;

        DataTable newrows = query.CopyToDataTable();
        newrows.TableName = "Table";

        DataSet returnDataSet = new DataSet();
        returnDataSet.Tables.Add(newrows);
        return returnDataSet;
    }

Next, the implementation of GetColumnValue. This also does a basic query over the data to get back the row in the table that corresponds to the team, column, and date specified.

    <WebMethod()> _
    Public Function GetColumnValue(ByVal team As String, ByVal column As String, ByVal dateGathered As String) As String
        Dim rows As DataTable = BugData.Tables("Table")

        Dim startDate As DateTime = System.Convert.ToDateTime(dateGathered)

        Dim query = From row In rows.AsEnumerable() _
                    Where row.Field(Of String)("Team") = team And _
                    row.Field(Of String)("Column") = column And _
                    System.DateTime.Compare(System.Convert.ToDateTime(row.Field(Of String)("Date")).Date, startDate.Date) = 0 _
                    Select row

        For Each row In query
            Return row.Field(Of String)("Value")
        Next

        Return "ERROR"
    End Function

    [WebMethod()]
    public string GetColumnValue(string team, string column, string dateGathered)
    {
        DataTable rows = BugData.Tables["Table"];

        DateTime startDate = System.Convert.ToDateTime(dateGathered);

        var query = from row in rows.AsEnumerable()
                    where row.Field<string>("Team") == team &&
                    row.Field<string>("Column") == column &&
                    System.DateTime.Compare(System.Convert.ToDateTime(row.Field<string>("Date")).Date, startDate.Date) == 0
                    select row;

        foreach (var row in query)
        {
            return row.Field<string>("Value");
        }

        return "ERROR";
    }

Finally, the implementation of GetDailyValues:

    <WebMethod()> _
    Public Function GetDailyValues(ByVal dateGathered As String) As DataSet
        Dim rows As DataTable = BugData.Tables("Table")

        Dim startDate As DateTime = System.Convert.ToDateTime(dateGathered)

        Dim query = From row In rows.AsEnumerable() _
                    Where System.DateTime.Compare(System.Convert.ToDateTime(row.Field(Of String)("Date")).Date, startDate.Date) = 0 And _
                    (row.Field(Of String)("Column") = "Active" Or _
                     row.Field(Of String)("Column") = "Incoming Rate (7 Day)" Or _
                     row.Field(Of String)("Column") = "Resolved Fixed Rate (7 Day)" Or _
                     row.Field(Of String)("Column") = "Resolved Rate (7 Day)") _
                     Order By row.Field(Of String)("Team"), row.Field(Of String)("Column") _
                    Select row

        Dim newrows As DataTable = New DataTable("Table")
        newrows.TableName = "Table"
        Dim i As Integer
        For i = 1 To 20
            newrows.Columns.Add(i.ToString())
        Next

        Dim objectArray(19) As Object
        i = 0
        For Each row In query
            objectArray(i) = row.Field(Of String)("Value")
            i = i + 1
        Next

        newrows.Rows.Add(objectArray)

        Dim returnDataSet As New DataSet()
        returnDataSet.Tables.Add(newrows)
        Return returnDataSet
    End Function

    [WebMethod()]
    public DataSet GetDailyValues(string dateGathered)
    {
        DataTable rows = BugData.Tables["Table"];
        DateTime startDate = System.Convert.ToDateTime(dateGathered);

        var query = from row in rows.AsEnumerable()
                    where System.DateTime.Compare(System.Convert.ToDateTime(row.Field<String>("Date")).Date, startDate.Date) == 0 &&
                    (row.Field<string>("Column") == "Active" ||
                     row.Field<string>("Column") == "Incoming Rate (7 Day)" ||
                     row.Field<string>("Column") == "Resolved Fixed Rate (7 Day)" ||
                     row.Field<string>("Column") == "Resolved Rate (7 Day)")
                    orderby row.Field<string>("Team"), row.Field<string>("Column")
                    select row;

        DataTable newrows = new DataTable("Table");
        newrows.TableName = "Table";
        int i;
        for (i = 1; i <= 20; i++)
        {
            newrows.Columns.Add(i.ToString());
        }

        object[] objectArray = new object[20];
        i = 0;
        foreach (var row in query)
        {
            objectArray[i] = row.Field<string>("Value");
            i = i + 1;
        }

        newrows.Rows.Add(objectArray);

        DataSet returnDataSet = new DataSet();
        returnDataSet.Tables.Add(newrows);
        return returnDataSet;
    }

You should be able to run the web service project and test it.

Now that we have a working web service, lets create a UDF that uses it.

1. VSTO Bug Tracker : The Data

2009-05-14T21:08:00Z

For my TechEd demo today, I will be showing a solution that helps me to do part of my job as a development manager: tracking the bug counts in the product. At Microsoft, we have an internal system that tracks a ton of relevant bug stats in TFS and takes a snapshot of various key metrics in that system every day—for example, active bugs for a team on a given day day. I will be writing a solution that brings that bug data into Excel and Word using VSTO so it can be further analyzed. Ways I can analyze it in Excel will include using Pivot Tables and Pivot Charts against the data, grabbing this data to use in custom formulas in Excel, etc. In Word, I can create nicely formatted bug mail that I can then send to the team.

First, a bit more about the bug data. The bug data is tracked in a table that has these columns: Team, Column, Date, and Value.

Team – The team we are tracking bugs for. On my team I have 5 teams I track bugs for:

Project - VSTA
Project - Office Server
Project - Office Client
Project - Pro Tools
Project - VBA

Column – The name of the bug statistic being tracked. Some examples:

Active – number of active bugs on a given day
Resolved Fixed Rate (7 Day) – the 7 day average of bugs that were resolved fixed
Resolved Rate (7 Day) – the 7 day average of total bugs were resolved (some bugs may be resolved without having an actual fix occur, for example because they are duplicate bugs)
Incoming Rate (7 Day) – the 7 day average of bugs that were opened

Date– The date the bug statistic was gathered.

Value – The value for the bug statistic on a given day, for example Active bugs for a particular team is 50 on a particular date.

So, for my particular team, if I want to know the Active bug count for VSTA on May 1st, I would get back this set of values—this row in the table.

<Team>Project - VSTA</Team>
<Column>Active</Column>
<Date>5/1/2009</Date>
<Value>98</Value>

If you are using Windows 7 RC and VSTO you better read this

2009-05-08T10:04:27Z

Saurabh blogs about how to work around an error you get if you published a VSTO solution using VS2008 on a machine running the Windows 7 RC and then you try to install the solution on any machine you will see the following error: “The required version of the .NET Framework is not installed on this computer”:

http://blogs.msdn.com/vsto/archive/2009/05/07/issues-with-installing-vsto-projects-that-were-published-from-visual-studio-2008-on-windows-7-rc-saurabh-bhatia.aspx