尝试获取AutoFilter日期筛选条件时出错1004 [英] Error 1004 when trying to get AutoFilter criteria of date filtering

问题描述

我正在研究一个VBA代码，它应该保存&恢复Excel中的当前AutoFilter状态。我一直在使用这指向 Jon von d呃Heyden的网站的解决方案，但网站提到：

未知（可能的日期TreeView过滤器） strong>：到目前为止，我无法找到一种方法来捕获日期过滤器，其中标准基于筛选下拉菜单中树视图控件的选择。这些标准不存储在Criteria1或Criteria2属性中。我想象，制定标准将涉及循环Range_Field值。尽管这将首先要求关闭所有其他字段过滤器，但从工作表函数中不能执行的操作，并且将涉及定时器来触发子程序。再次，我选择避免额外的复杂性。

解决方案

存储自动过滤器的另一种方法状态（包括日期过滤器的树形视图选择）是使用自定义视图。

 '[您要运行的任何代码捕获自动过滤器设置] 
 
 wkbExample.CustomViews.Add ViewName：=cvwAutoFilterSettings，RowColSettings：= True 
 
'[您要使用自动过滤器或否运行的任何代码autofilter] 
 
 wkbExample.CustomViews（cvwAutoFilterSettings）。显示
 wkbExample.CustomViews（cvwAutoFilterSettings）。删除
 
'[您要运行的任何代码恢复原始自动过滤器设置后$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 9489126 / in-excel-vba-how-do-i-save-restore-a-user-defined-filter / 31006447＃31006447> Cyious的原始帖子 
 
 
 
但是有三个条件离子使其工作：
 
 
工作簿中的任何工作表都不应受保护
 
 
在显示自定义视图之前不应该清除/删除自动过滤器应用的字段
 
 
您不能有任何ListObjects范围格式化为表）在您的工作簿中的任何工作表上。如果您至少有一个ListObject，则自定义视图功能将被完全禁用，并且任何VBA调用将导致运行时错误1004.在保存自定义视图之前，您必须.UnList列出所有的ListObject。
 
 
奖励：窗口中的列宽，冻结窗格位置和范围位置都存储在自定义视图中。
 
I'm working on a piece of VBA code which should save & restore the current AutoFilter state in Excel. I've been using the code here for a looong time without any issues, but now I've run into a pretty serious one. Let me illustrate that...

Assume you have a (very simple) table setup with date filtering:


If you would like to get the filtering criteria used programmatically, it will fail:


This is happening for me in Excel 2010. Does anyone know a workaround for this?

A link for the same issue described by someone else on Microsoft TechNet: Excel VBA AutoFilter criteria when Operator is xlFilterValues for Dates This points to Jon von der Heyden's site for a solution, but the site mentions:

  
Unknown (likely date TreeView filter): To-date I am unable to find a way to capture date filters where the criteria is based on selection from the Tree View control in the Filter drop-down. These criteria are not stored in the Criteria1 or Criteria2 properties. I imagine working out the criteria will involve looping the Range_Field values. Although this would first require that all other field filters be turned-off, something not do-able from a worksheet function and would involve a timer to trigger a sub-routine. Again, I chose to avoid the extra complexity.

解决方案
An alternative way to store the autofilter state (including the treeview selection of date filters) is to use a custom view.
'[whatever code you want to run before capturing autofilter settings]

    wkbExample.CustomViews.Add ViewName:="cvwAutoFilterSettings", RowColSettings:=True

    '[whatever code you want to run with either your autofilter or no autofilter]

    wkbExample.CustomViews("cvwAutoFilterSettings").Show
    wkbExample.CustomViews("cvwAutoFilterSettings").Delete

    '[whatever code you want to run after restoring original autofilter settings]
Original post by Cyious

However there are three conditions to make it work:

None of the sheets in the workbook should be protected 
The fields to which the autofilter was applied should not be cleared/deleted before showing the custom view
You can not have any ListObjects (i.e. ranges "formatted as table") on any sheet in your workbook. If you have at least one ListObject, the CustomViews feature is disabled altogether and any VBA call to it will cause runtime error 1004. You would have to .UnList all of your ListObjects before saving the custom view.
Bonus: also column width, freeze pane position and range location within the window are stored in the custom view.

                        
这篇关于尝试获取AutoFilter日期筛选条件时出错1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！