用于记住已激活的过滤器的代码 [英] Code to remember filters that are activated
问题描述
HI
我有一个更新excel表的SQL查询 - 为了正确执行 - 我需要确保删除所有过滤器。
I have a sql query that updates an excel table- for it to do it correctly- I need to make sure all filters are removed.
使用以下代码
using the below code
如果ActiveSheet.AutoFilterMode或ActiveSheet.FilterMode则为
ActiveSheet.ShowAllData
结束如果
If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
有没有办法可以记住被激活的过滤器,以便在刷新后我可以重新激活它们?
Is there a way I can get the filters that were activated remembered so that after the refresh I can reactivate them?
谢谢
David
推荐答案
首先修正你的示例代码。两个条件都必须为True或ShowAllData代码将出错。因此,使用"And"和"And"。在If语句中,如下所示。
Firstly a correction in your example code. Both conditions must be True or the ShowAllData code will error. Therefore use "And" in the If statement like the following.
如果ActiveSheet.AutoFilterMode 和ActiveSheet.FilterMode则为
ActiveSheet.ShowAllData
结束如果
If ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
关于保存过滤器的问题,可以通过将过滤器保存到单独的工作表来完成。但是,各种操作员确实需要知道如何设置过滤器。即简单的单一标准,两个标准或值列表
,就像在单个过滤器上选择多个标准一样。
On the issue of saving the filter, this can possibly be done by saving the filters to a separate worksheet. However, with various operators really need to know how the filters are being set. ie simple single criteria, two criteria or a list of values as when multiple criteria is selected on a single filter.
到目前为止,我还无法使用VBA代码设置日期过滤器列表。不是说这是不可能的,但我已经在论坛上提出这个问题并且到目前为止还没有收到与日期列表一起使用的回复(个别日期可以,但不是
日期列表。)
So far, I have not been able to use VBA code to set a list of Date Filters. Not saying it is impossible but I have asked the question on forums and have so far not received a reply that works with a list of dates (Individual dates are OK but not a list of dates.)
最简单的描述过滤器设置的方法是打开宏录制器并设置所有过滤器,然后关闭录像机并将录制的代码复制到您的回复中。或者,如果您使用VBA代码设置过滤器
,则提供该副本。
Easiest way to describe how your filters are set is to turn on the macro recorder and set all of the filters and then turn off the recorder and copy the recorded code to your reply here. Alternatively, if you are using VBA code to set the filters then provide a copy of that.
这篇关于用于记住已激活的过滤器的代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!