Excel VBA - 可更改日期过滤器 [英] Excel VBA - Change date filter of a pivottable
问题描述
我在我的工作表上有一个关键点,我正在尝试添加一个日期过滤器:
Private Sub foo
Dim MyDate
MyDate =#1/27/1993#
工作表(时间轴)。数据透视表(PivotTable7)。PivotFields(EndDateFormatted)PivotFilters。添加类型:= xlBetween,Value1:= MyDate,Value2:= MyDate
End Sub
这是否导致无效过程调用或参数错误。我做错了什么?
更新:
thx luke_t,现在我知道我必须使用xlDateBetween。
现在我的代码看起来像:
Private Sub foo()
Dim d As Date
Dim dd As Date
d = DateSerial(2016,1,1)
dd = DateSerial(2016,1,2)
工作表(Timeline)。数据透视表( PivotTable7)PivotFields(EndDateFormatted)PivotFilters.Add类型:= xlDateBetween,Value1:= d,Value2:= dd
End Sub
现在说您输入的日期不是有效日期(1004)。
您需要将 xlDateBetween
> c code code code code code code code code code $ c =https://msdn.microsoft.com/en-us/library/office/ff193582.aspx =nofollow>以下是可以传入类型参数的常量名称。
使用带有 Type:=
参数的字符串。请参见下文。
Private Sub foo()
Dim d As String
d =数据透视表(PivotTable7)PivotFields(EndDateFormatted)PivotFilters.Add类型:= xlDateBetween,Value1:= d,Value2:= d
End Sub
I have a pivottable on my sheet and I'm trying to add a date filter:
Private Sub foo()
Dim MyDate
MyDate = #1/27/1993#
Worksheets("Timeline").PivotTables("PivotTable7").PivotFields("EndDateFormatted").PivotFilters.Add Type:=xlBetween, Value1:=MyDate, Value2:=MyDate
End Sub
How ever this results in an "invalid procedure call or argument" error. What am I doing wrong?
Update: thx luke_t, now I know that I have to use xlDateBetween. Now my code looks like:
Private Sub foo()
Dim d As Date
Dim dd As Date
d = DateSerial(2016, 1, 1)
dd = DateSerial(2016, 1, 2)
Worksheets("Timeline").PivotTables("PivotTable7").PivotFields("EndDateFormatted").PivotFilters.Add Type:=xlDateBetween, Value1:=d, Value2:=dd
End Sub
Now it says "the date you entered is not a valid date" (1004).
You need to pass xlDateBetween
into the Type:=
parameter of the PivotFilters.Add
method.
Here is a list of constant names that can be passed into the type parameter.
Use a string with the Type:=
parameter. See below.
Private Sub foo()
Dim d As String
d = "01/01/2016"
Worksheets("Timeline").PivotTables("PivotTable7").PivotFields("EndDateFormatted").PivotFilters.Add Type:=xlDateBetween, Value1:=d, Value2:=d
End Sub
这篇关于Excel VBA - 可更改日期过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!