Save and Restore AutoFilter

[georg]

Hi

I only just got aquainted with Excel DNA (first macro in the
distribution's readme). I am working on my first Excel Template
Project with VS 2010 VSTO (C#), and I run into an unexpected problem -
is it possible that I can solve it with Excel DNA? If so, I should be
grateful for some pointers on how to do it.

The problem is as follows.

I have a sheet with a databound ListObject. When I set filters on the
table using the controls in the header row, these do not get saved
with the workbook; when I reopen the workbook the filter settings are
gone.

I have tried writing code to save the filter settings
(myListObject.AutoFilter.Filters) in a [Cached] member variable and
restore them from there on reopen.

This workes in most cases but fails when I try to do the equivalent of
the following recorded macro:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1,
Criteria1:= _
Array("A", "B", "E"), Operator:=xlFilterValues

The problem seem to be twofold:
1. The caching mechanism seems not to like the Array("A",...) (shown
as object{object[]} in VS); nor have I found a way to convert it into
something cacheable.
2. The Microsoft.Office.Interop.Excel.Range.ApplyFilter method does
not accept the xlFilterValues operator.

[georg]

> 2. The Microsoft.Office.Interop.Excel.Range.ApplyFilter method does
> not accept the xlFilterValues operator.

should of course read:
2. Microsoft.Office.Interop.Excel.Range.AutoFilter method does

[Govert van Drimmelen]

Hi,

Firstly, Excel-DNA and the VSTO libraries don't get along well in the
same add-in (they're fine in the same Excel instance, though). If you
referencing the VSTO libraries (Microsoft.Office.Tools.*) from your
Excel-DNA add-in, things will get difficult. So your "first Excel
Template Project with VS 2010 VSTO" might be a problem in the Excel-
DNA context already.

Next, I don't know much about the [Cached] variables in VSTO. I
believe it is implemented through the CustomXMLParts support in the
Excel 2007/2010 file formats. You should be able to read and write
this with the regular COM interface (without the VSTO wrappers)
through Workbook.CustomXMLParts, but I haven't tried anything like
this myself. This StackOverflow question might help:
http://stackoverflow.com/questions/1067719/how-to-store-information-in-a-excel-woorkbook

So, together with some serialization that you need to check, that
should deal with the saving and restoring of the setting information
in a workbook.

To apply them again you have to make the call to AutoFilter. I'm not
sure about the exact parameter, but a one-dimensional object array
(object[]) should marshal properly. The issue with the xlFilterValues
option might have to do with the exact version of the Primary Interop
Assemblies that you are referencing. If the option was added only in
Excel 2010, and you are referencing the Excel 2007 assemblies, you
might find the option unavailable.

I'd suggest you separate the issues of storing and retrieving the
settings, and then applying the AutoFilter. If you're still stuck with
either, I can have a closer look.

Regards,
Govert

[georg]

Hi Govert

Thanks for the quick reply.

1. I will follow up on your pointer concerning the caching issue.

2. Concerning Microsoft.Office.Interop.Excel.Range.AutoFilter:
The Excel 2010 Primary Interop Assembly documentation for the
Range.AutoFilter method at:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.autofilter
has an explicit list of allowed XlAutoFilterOperator values which
shows only 6 of the 11 values in the Excel 2010 documentation at:
http://msdn.microsoft.com/en-us/library/ff839625.aspx
and does not include the xlFilterValues value.

3. Concerning compatibility of Excel DNA with VSTO libraries:
I was thinking of writing an Excel DNA macro with a c# signature like
void MyApplyFilters(string rangename, MyFilterClass[] filters)
{
}
(no Microsoft.Office.Interop.Excel or Microsoft.Office.Tools.Excel
types referenced directly or indirectly)
Do I correctly understand that this should not be a problem? and that
it would allow me to use the full Excel 2010 functionality (as opposed
to the microsoft.office.interop.excel functionality)?

Regards - Georg

On Jun 4, 4:36 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>
> Firstly, Excel-DNA and the VSTO libraries don't get along well in the
> same add-in (they're fine in the same Excel instance, though). If you
> referencing the VSTO libraries (Microsoft.Office.Tools.*) from your
> Excel-DNA add-in, things will get difficult. So your "first Excel
> Template Project with VS 2010 VSTO" might be a problem in the Excel-
> DNA context already.
>
> Next, I don't know much about the [Cached] variables in VSTO. I
> believe it is implemented through the CustomXMLParts support in the
> Excel 2007/2010 file formats. You should be able to read and write
> this with the regular COM interface (without the VSTO wrappers)
> through Workbook.CustomXMLParts, but I haven't tried anything like

> this myself. This StackOverflow question might help:http://stackoverflow.com/questions/1067719/how-to-store-information-i...

> > not accept the xlFilterValues operator.- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi Georg,

If you have a reference to the right version of the Primary Interop
Assembly (for Excel 2010 it's version 14.0), I don't think there is a
problem with the AutoFilter command. The documentation you point to
might be misleading - that version of the PIA does indeed have all the
options for XlAutoFilterOperator, and the xlFilterValue operator works
fine.

In terms of access to the COM API you are in the same position with
Excel-DNA as with any other .NET-based add-in project (which is the
same access via COM you have from VBA too). Excel-DNA additionally
gives you access to the Excel C API, but that is not helpful in this
case since the C API has not been updated with new features like the
ListObject/Tables from recent versions.

So you should be able to "use the full Excel 2010 functionality"
equivalently from VBA, VSTO or Excel-DNA. On top of that, Excel-DNA
gives you another option - the C API.

I tried the following, to explore the API and make sure the AutoFilter
works as expected. Then I used the .NET 4 dynamic support to make the
COM calls, and finally the PIA assemblies.

1. VBA
------
Make a sheet with two columns, with the first column including some
values like "A" and "E":
Left Right
A 1
B 2
C 3
D 1
E 2
A 3
B 1
C 3
D 4

Select the data and insert a table (with headers)

Create a VBA subroutine:
Sub ApplyFilter()

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1,
Criteria1:= _
Array("A", "B", "E"), Operator:=xlFilterValues

End Sub

Run with F5 and check that it worked as expected.

2. Excel-DNA with C# 4 dynamic COM
----------------------------------
Copy ExcelDna.xll from the distribution into a folder, and rename to
AutoFilter.xll

Create a new text file in the same directory, called AutoFilter.dna

<DnaLibrary RuntimeVersion="v4.0" Language="C#" >
<![CDATA[
using System;
using ExcelDna.Integration;

public static class Test
{
[ExcelCommand(MenuText="DoAutoFilter")]
public static void DoAutoFilter()
{
// From docs or VBA Object Browser
int xlFilterValues = 7;
object[] criteria = new[] {"A", "B", "E"};

dynamic app = ExcelDnaUtil.Application;
app.ActiveSheet.ListObjects["Table1"]
.Range.AutoFilter(
Field: 1,
Criteria1: criteria,
Operator: xlFilterValues);
}
}
]]>
</DnaLibrary>

This creates a menu item (under the Add-Ins tab) that does the
filtering.

Note I'm using the 'dynamic' support and option arguments feature
from .NET 4. This means the COM call is late-bound.
All works as expected.

3. Excel-DNA with the Excel Primary Interop Assembly (PIA)
----------------------------------------------------------

<DnaLibrary RuntimeVersion="v4.0" Language="C#" >
<Reference Name="Microsoft.Office.Interop.Excel" />
<![CDATA[
using System;
using Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;

public static class Test
{
[ExcelCommand(MenuText="DoAutoFilter")]
public static void DoAutoFilter()
{
// From docs or VBA Object Browser
object[] criteria = new[] {"A", "B", "E"};

Application app = (Application)ExcelDnaUtil.Application;
Worksheet activeSheet = (Worksheet)app.ActiveSheet;
activeSheet.ListObjects["Table1"].Range.AutoFilter(
Field: 1,
Criteria1: criteria,
Operator: XlAutoFilterOperator.xlFilterValues);
}
}
]]>
</DnaLibrary>

4. Excel-DNA with the Excel PIA - .NET 2.0 syntax
-------------------------------------------------

<DnaLibrary Language="C#" >
<Reference Name="Microsoft.Office.Interop.Excel" />
<![CDATA[
using System;
using Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;

public static class Test
{
[ExcelCommand(MenuText="DoAutoFilter")]
public static void DoAutoFilter()
{
// From docs or VBA Object Browser
object[] criteria = new object [] {"A", "B", "E"};

Application app = (Application)ExcelDnaUtil.Application;
Worksheet activeSheet = (Worksheet)app.ActiveSheet;
activeSheet.ListObjects["Table1"].Range
.AutoFilter(1, criteria,
XlAutoFilterOperator.xlFilterValues,
null, null);
}
}
]]>
</DnaLibrary>

All of this can of course be done in your Visual Studio project too,
and can also be triggered from a Ribbon button, a shortcut key or an
event.

If I need to explain any of this in more detail, please ask.

Regards,
Govert

On Jun 5, 11:22 am, georg <g.wi...@bluewin.ch> wrote:
> Hi Govert
>
> Thanks for the quick reply.
>
> 1. I will follow up on your pointer concerning the caching issue.
>
> 2. Concerning Microsoft.Office.Interop.Excel.Range.AutoFilter:
> The Excel 2010 Primary Interop Assembly documentation for the

> Range.AutoFilter method at:http://msdn.microsoft.com/en-us/library/microsoft.office.interop.exce...