VSTO Visual Studio Tools for Office
18-Nov-1313 Leave a comment
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
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
Stephen Shakespeare 