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.