Leon Lai Kan
Posts: 200
Joined: 2018-12-20
|
Hi,
I am studying this blog sample:
Working with Excel tables & ranges: VB.NET code samples
https://www.add-in-express.com/creating-addins-blog/2013/10/24/excel-tables-ranges-vbnet/
In particular, I am studying the section "Filter a Table".
Here is the SUB provided by the sample:
Private Sub FilterTable(sheet As Excel.Worksheet, _
tableName As String, _
fieldName As String, _
filter As String)
Dim table As Excel.ListObject
table = sheet.ListObjects(tableName)
table.Range.AutoFilter(fieldName, filter) '//Error points to here: filter
Marshal.ReleaseComObject(table)
End Sub
To see the code in action, I created a ribbon button with the following code:
Private Sub FilterAtable_OnClick(ByVal sender As System.Object, _
ByVal control As AddinExpress.MSO.IRibbonControl, _
ByVal pressed As System.Boolean) Handles FilterAtable.OnClick
Dim MySheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
Dim MytableName As String = "Table1"
Dim MyFieldName As String = "Item"
Dim MyFilter As String = "A" '//My table shows A, B, C, D in successive rows
FilterTable(MySheet, MytableName, MyFieldName, MyFilter)
End Sub
When I run the program, it crashes and the error points to "filter" in the 1st SUB
table.Range.AutoFilter(fieldName, filter) '//Error points to here: filter
Error Msg: "_Autofilter method of Range class failed."
red
In the Visual Studio Object Browser, I see that the Function Autofilter has the following optional parameters:
Autofilter( [Field As Object],
[Criteria1 As Object],
[Operator As ...],
[Criteria2 As Object],
[VisibleDropDown As Object] )
As Object
Thanks
Leon |
|
Dmitry Kostochko
Add-in Express team
Posts: 2887
Joined: 2004-04-05
|
Hi Leon,
Thank you for the detailed description.
I think it is a bug in the FilterTable() method. It uses the Range.AutoFilter() method that requires not a field name but the integer offset of the field on which you want to base the filter:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.autofilter?view=excel-pia
Please try to call the method as follows:
Private Sub FilterAtable_OnClick(ByVal sender As System.Object, _
ByVal control As AddinExpress.MSO.IRibbonControl, _
ByVal pressed As System.Boolean) Handles FilterAtable.OnClick
Dim MySheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)
Dim MytableName As String = "Table1"
'Dim MyFieldName As String = "Item"
Dim MyFieldName As String = "1"
Dim MyFilter As String = "A" '//My table shows A, B, C, D in successive rows
FilterTable(MySheet, MytableName, MyFieldName, MyFilter)
End Sub
Meanwhile we will review the code and update the blog post and example. |
|
Leon Lai Kan
Posts: 200
Joined: 2018-12-20
|
Hi, Dmitry
Now the code works perfectly!
No need to review your code. The error is in MY code (the button code).
The sample does not provide ribbon button codes.
Thanks a lot and Best Regards,
Leon |
|