none
VSTO - Excel - AutoFilter - Trying to get Date filter criteria (year, month or day) throws exception RRS feed

  • Question

  • I'm trying to get the filters of a table in order to reapply them after some modifications. Everything is ok, but the trouble begins when the filter is on a Date column. This is the way I'm doing it:

    for (int i = 1; i <= filters.Count; i++)
    {
      FilterTemp f = new FilterTemp();
      f.On = filters[i].On;
    
      if (f.On)
      {
        f.Field = i;
    
        try
        {
          f.Criteria1 = filters[i].Criteria1;
        }
        catch { }
    
        f.Operator = (int)filters[i].Operator;
    
        try
        {
          f.Criteria2 = filters[i].Criteria2;
        }
        catch { }
      }
    
      fs.Add(f);
    }


    When the filter is on a text or number column, everything works beautifully, but when a date column is filtered by year, month or day, I get an exception on line 5 when trying to get the "Criteria1".

    I tried to change the operator to xlFilterDynamic, as mentioned on an answer of this MSDN post: https://social.msdn.microsoft.com/Forums/vstudio/en-US/15ec8d69-3e6f-450d-82c0-ca53e63c8f64/getting-data-of-list-object-filters-for-date-column?forum=vsto

    Something like this:

    for (int i = 1; i <= filters.Count; i++)
    {
      FilterCache f = new FilterCache();
      f.On = filters[i].On;
    
      if (f.On)
      {
        f.Field = i;
    
        try
        {
          f.Criteria1 = filters[i].Criteria1;
        }
        catch
        {
          filters[i].Operator = XlAutoFilterOperator.xlFilterDynamic;
    
          f.Criteria1 = filters[i].Criteria1;
        }
    
        f.Operator = (int)filters[i].Operator;
    
        if (f.Operator == 0)
          f.Operator = (int)XlAutoFilterOperator.xlAnd;
    
        try
        {
          f.Criteria2 = filters[i].Criteria2;
        }
        catch { }
      }
    
      fs.Add(f);
    }

    No success. The filters[i].Criteria1 is now returning 1 forever, it doesn't matter the filter I use on that date column.

    In order to simulate this issue, it is necessary to create a table on an Excel worksheet and then put some random dates in a column. Then, filter this column selecting at least 3 different dates. Run the code.

    There's already a post about this on stackoverflow: Excel VSTO - accessing AutoFilter's Array Criteria throws exceptions

    Also at: https://social.msdn.microsoft.com/Forums/office/en-US/281fdbc5-6535-497f-b427-f69f4b092e24/excel-vsto-accessing-autofilters-array-criteria-throws-exceptions

    ... But there are no satisfactory answers or maybe it is a little bit difficult to understand the question.

    FilterTemp class:

    public class FilterTemp
    {
      public bool On;
      public object Field;
      public object Criteria1;
      public int Operator;
      public object Criteria2;
    }


    Wednesday, April 18, 2018 2:17 PM

All replies

  • Hello eric.milaneze,

    Yes, this issue does exist. While filtering a date column using checkboxes, you are using an array with specific format to filter the column. However, we could not get the array from the filter object.

    This is just as Pradeep said in the your second shared link. For workaround for this issue, as Chenchen said in first link, you could try to use an array to record current show row. It will work well if you does not change any value of the filter column while re-applying the filter condition.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 19, 2018 6:23 AM
  • Hi Terry,

    Thank you very much for your response.

    I "recreated" this thread because I was uncomfortable with that workaround. It is slow and, as you said, it won't work well if the user changes any value of the filter.

    Let's say the user filters by year and then he enters a new date of that year. When reapplying, we won't see that specific date, even if it belongs to the year that was filtered.

    I thought I was doing something wrong when trying to get the filters, but it seems like that's a bug, am I right?

    Regards,

    Eric

    Thursday, April 19, 2018 2:07 PM
  • Hello eric.milaneze,

    As far as I know, it is not possible to get such filter condition from filter now. You could go to File->Feedback to submit a feedback for this issue. 

    Anyway, we have to take some workarounds to try to meet our requirements on recording filter condition on data column at present. My workaround just provides an idea. You could try to adjust it according to your situation.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 1:40 AM