VSTO Visual Studio Tools for Office

Post is a placeholder for my VSTO sample code

Quick VSTO notes:

After adding a ribbon and a button to refer to code in “ThisAddIn.vb” use Globals.ThisAddIn.GetCode

In ThisAddIn.vb to get the work sheet
Dim worksheet As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet

How do I align a range and make other format changes?

        With worksheet.Range("A2:Y2")
            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            .WrapText = True
            .ColumnWidth *= 2
            .RowHeight *= 3
        End With



Walkthrough: Complex data binding in an application level project


This worked fine:

    Private Sub SetListObjectAndBinding()
        Using ta As New myTableAdapters.myTableAdapter
        End Using

        Dim ExtendedWorksheet = Globals.Factory.GetVstoObject(worksheet)
        Dim cell As Excel.Range = ExtendedWorksheet.Range("$A$1:$B$10", System.Type.Missing)
        myListObject = ExtendedWorksheet.Controls.AddListObject(cell, "list1")

        myItemBindingSource = New System.Windows.Forms.BindingSource
        myItemBindingSource.DataSource = myDS.MyTable

        myItemListObject.AutoSetDataBoundColumnHeaders = True
        myItemListObject.SetDataBinding(Me.vehicleItemBindingSource, "", "Field1", "Field2", "Field3")
    End Sub

Then normal Excel editing and a subsequent dataset update method works a treat.
The following post suggested that the ListObject has a ChangedEvent


How do I working with the ListObject

How do I change Column Headers and other properties in a ListObject?

You cannot change the column header if the following is True

Also the locked will work when the worksheet.Protect and Unprotect, but don’t change any cells when Protect is on.

        VehicleItemListObject.AutoSetDataBoundColumnHeaders = False

        For Each col As Excel.ListColumn In VehicleItemListObject.ListColumns
            myRange = col.DataBodyRange
            If col.Index < FirstWriteColumn Then
                myRange.Locked = True
                myRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(Drawing.Color.LightGray)
                myRange.Locked = False
            End If
            'Select Case col.Name
            '    Case "YearFrom"
            '        col.Name = "Year From"
            'End Select
            If col.Name.StartsWith("Year") AndAlso col.Name <> "Year" Then
                col.Name = col.Name.Replace("Year", "Year ")
            End If
            If col.Name.Length > 14 Then
                myRange = worksheet.Columns(col.Index)
                myRange.ColumnWidth *= 1.5
            End If

How do I turn off Auto Filter after loading a ListObject?

VehicleItemListObject.ShowAutoFilter = False

Why am I using VSTO?

A problem with taking a de-normalised spreadsheet and normalising it is that the new UI will likely be parent row with multiple children. This means the user will now only be able to edit one set of children at a time without doing further work. To resolve this I am trying a pivot view of the normalised data which may then be exported to Excel, which can then be re-saved back to the data.


WPF EntityFramework DataGrid ComboBox

There may be several ways of doing this:

        <CollectionViewSource x:Key="originatorViewSource" d:DesignSource="{d:DesignInstance my:Originator, CreateList=True}" />
        <CollectionViewSource x:Key="customerLookup" />
                    <DataGridComboBoxColumn x:Name="cboCustomer"
                                            ItemsSource="{Binding Source={StaticResource customerLookup}}"
                                            SelectedValueBinding="{Binding Path=CustomerID}"

        private void LoadComboBox()
                SalesContext contextCustomer = new SalesContext();
                var customerList = from c in contextCustomer.Customers
                                   orderby c.Customer1
                                   select new { ID = c.ID, Customer1 = c.Customer1 };

                CollectionViewSource CustomerViewSource = (CollectionViewSource)this.Resources["customerLookup"];
                CustomerViewSource.Source = customerList.ToList();
                //cboCustomer.ItemsSource = customerList.ToList();
            catch (Exception ex)



WPF Quick Reminders

Windows Forms Integration

Dim myControl1 = New WpfControlLibrary.CustomControl1
 myControl1.Content = "Hello World"
Me.ElementHost1.Child = myControl1


You may want HorizontalAlignment or VerticalAlignment = “Stretch”


Imports System.Data.Entity

private AccountTypeContext _context = new AccountTypeContext();
private CollectionViewSource myCollectionViewSource;
//Next in Loaded. If in user control then:
// Do not load your data at design time.
if (!System.ComponentModel.DesignerProperties.GetIsInDesignMode(this))
{ }
 myCollectionViewSource = (CollectionViewSource)this.Resources["accountTypeViewSource"];
myCollectionViewSource.Source = _context.AccountTypes.Local;

DataBinding – BindingNavigator Functions

        private void MoveFirstButton_Click(object sender, RoutedEventArgs e)
        private void MovePreviousButton_Click(object sender, RoutedEventArgs e)
        private void MoveNextButton_Click(object sender, RoutedEventArgs e)
        private void MoveLastButton_Click(object sender, RoutedEventArgs e)

It may be necessary to use


DataBinding – Filter

With acknowledgment to:




In _Loaded

       myCollectionViewSource.Filter += new FilterEventHandler(FilterTextBoxLike);
In Code - assuming you have a text box called FilterTextBox
        #region Filter
        bool FilterClearing;
        private void FilterTextBox_TextChanged(object sender, TextChangedEventArgs e)        {
            try             {
                if (FilterClearing == false)                    {
                        RefreshList();                    }
            catch (Exception ex)            {

        private void RefreshList()

        private void FilterTextBoxLike(object sender, FilterEventArgs e)
            AccountType at = e.Item as AccountType;

            if (at.AccountType1.ToLower().Contains(FilterTextBox.Text.ToLower()))
                e.Accepted = true;
                e.Accepted = false;

        private void ClearFilterButton_Click(object sender, RoutedEventArgs e)
		FilterClearing = true;
               FilterTextBox.Text = "";
            catch (Exception ex)
            FilterClearing = false;



Data Grid – Styles


DatePicker as UK style date

Two actions seem to be required

Imports System.Globalization
Imports System.Threading

Namespace My

Partial Friend Class MyApplication

Private Sub MyApplication_Startup(sender As Object, e As Microsoft.VisualBasic.ApplicationServices.StartupEventArgs) Handles Me.Startup
Dim ci = New CultureInfo(Thread.CurrentThread.CurrentCulture.Name)
ci.DateTimeFormat.ShortDatePattern = “dd-MMM-yyyy”
Thread.CurrentThread.CurrentCulture = ci
End Sub

End Class

End Namespace

<DatePicker Name=”DateReportedTextBox” Text=”{Binding Path=DateReported, StringFormat={}{0:dd-MMM-yyyy}}” />


Database Unit Test


Cool – Easy – Very Useful



VS TFS Build The working folder .. is already in use by the workspace on computer

I had an earlier issue in previous post for build being unable to edit work items. So I changed the Build Service account in TFS Administration Console, which then gave me a new problem when running a build:

“The working folder .. is already in use by the workspace <workspacename;owner> on computer”

To resolve this I used Visual Studio Command Prompt

tf workspaces /server:http://localhost:8080/tfs /owner:* /format:detailed

I then had problem with the following syntax:

tf workspace /delete /server:http://localhost:8080/tfs 1_1_<name>;”NT AUTHORITY\SYSTEM”

Was not sure if the 1_1_ was ok, but the problem was that the I needed the “NT AUTHORITY\SYSTEM” and I needed it in quotes. After deleting the workspace the world became right again.



There is a program Team Foundation Sidekicks 2010, by Attrice Corporation, but in practice I did not need this.

VS TFS The work item cannot be saved because at least one field contains a value that is not allowed

You may get this bug:

  1. if you have a Build > Process > Advanced > Create Work Item on Failure
  2. possibly also if you are checking-in code against work items

This occurs because the account performing the build does not have permission to create/update the work items

To solve this I believe that you

  1. Give the account running the build the correct permission, probably in TFS Adminstration Console > Team Project Collections > <your project>
  2. Either change the account running the build in TFS Administration Console > Build Configuration


Problem with Microsoft Report on Terminal Server




Entity Framework 6.x from 4.x DbExtensions is not declared. It maybe inaccessible due to its protection level.

After upgrade from Entity Framework 4.x to 6.x get various errors:


myBindingSource.DataSource = DbExtensions.ToBindingList(context.myItems)


me.myDataGridView.DataSource = context.myItems.Local.ToBindingList