[AccessD] Autofilters in Excel

I've been doing a bit of Excel reporting from Access but I ran into a bit of an issue.  I export data to a series of Excel sheets in a single workbook.  The sheets are identical except for a master summary sheet.
 
I'd written a form interface that applies any combination of single restrictions on one through eight potential autofiltered columns to all the data sheets and shows the filtered subtotals on the front page.  This much works great.
 
Upon reflection, it occurred to me that I could display a toolbar button when a user is on any data sheet and it would be significantly less code to allow a user to set any combination of autofilters desired on a work sheet and use the code to duplicate that autofilter to all data sheets (the form code does this already for single criteria) and display the summary sheet results.
 
When Autofilters are On there are potentially two Criteria named Criteria1 and Criteria2.  Checking a criterion when for one of the filters when that filter is not set and and the or the second criterion has no value triggers an error.  MSDN has some example code for working around this at msdn.microsoft.com/en-us/library/bb177404.aspx:
 
With Worksheets("Crew")
    If .AutoFilterMode Then
        With .AutoFilter.Filters(1)
            If .On And .Operator Then
                c2 = .Criteria2
            Else
                c2 = "Not set"
            End If
        End With
    End If
End With
 
The problem is, if a user chooses the 'Top 10' criterion from the user interface, it turns out that the .Operator property evaluates to 3 and .On to true and the example breaks.
 
It turns out the AND and OR operator constants are 1 and 2 respectively and the top, bottom and top and bottom percent constants evaluate to 3 through 6.
 
The MSDN sample check for a 2nd criterion should read:
 
If .On And .Operator < 3 Then

It would be nice if they posted sample code that works.
 
Jurgen Welz
_________________________________________________________________
We are your photos. Share us now with Windows Live Photos.
http://go.microsoft.com/?linkid=9666047