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

.

Links

Walkthrough: Complex data binding in an application level project

http://msdn.microsoft.com/en-us/library/cc668212(v=vs.100).aspx

This worked fine:

    Private Sub SetListObjectAndBinding()
        Using ta As New myTableAdapters.myTableAdapter
            ta.Fill(myDS.myTable)
        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

http://social.msdn.microsoft.com/Forums/vstudio/en-US/252d6c46-a012-4278-a652-8175693c962a/how-do-i-detect-the-cell-data-change-on-the-excel-grid?forum=vsto

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)
            Else
                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
        Next

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.

End

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: