使用 VBA 在数据透视表中的两个日期之间进行过滤。英国到美国的日期格式问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22938973/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filtering between two dates in pivot table using VBA. UK to US date format issue
提问by JJ Moore
I have found a workaround for this but if anyone has a cleaner way of doing this I'd be interested/grateful.
我找到了一个解决方法，但如果有人有更简洁的方法来做到这一点，我会感兴趣/感激。
I have two boxes on a spreadsheet, called DateFrom and DateTo for use with a Date filter in a pivot table. Both are in the format DD/MM/YYYY. The data feeding into the table is also in this format. The pivot table also returns the dates in the same format. All my settings are in English UK and I've tried this on a separate machine with the same results.
我在电子表格上有两个框，称为 DateFrom 和 DateTo，用于与数据透视表中的日期过滤器一起使用。两者都采用 DD/MM/YYYY 格式。输入表格的数据也是这种格式。数据透视表还以相同的格式返回日期。我所有的设置都在英国英语中，我已经在另一台机器上尝试过，结果相同。
I then use the following code to feed into the pivot table:
然后我使用以下代码输入数据透视表：
ActiveSheet.PivotTables("Pivot").PivotFields("Date").PivotFilters. _
Add Type:=xlDateBetween, Value1:=Range("DateFrom").Value, Value2:=Range("DateTo").Value
The code reads the two cells as string and then applies this to the pivot table as if they were US format (MM/DD/YYYY). Ridiculous!
该代码将两个单元格读取为字符串，然后将其应用于数据透视表，就好像它们是美国格式 (MM/DD/YYYY)。荒谬的！
This is how I got around it:
我是这样解决的：
I had two more cells looking at these two cells in numeric format and aimed the code at these two cells instead. Not sure about any of you but I would struggle to convert any date into numeric in my head...!
我还有两个单元格以数字格式查看这两个单元格，并将代码瞄准这两个单元格。不确定你们中的任何一个，但我很难将任何日期转换为我脑海中的数字......！
If you have a simplified solution and/or explanation to this utter craziness I'd be much obliged!
如果您对这种完全疯狂的行为有一个简化的解决方案和/或解释，我将非常感激！
采纳答案by MP24
Indeed, this is strange. I've reproduced your issue with Excel 2007 and German locale settings.
的确，这很奇怪。我已经用 Excel 2007 和德语区域设置重现了您的问题。
However, when converting the date values to Longs using CLng, everything works:
但是，当使用 将日期值转换为Longs 时CLng，一切正常：
ActiveSheet.PivotTables("Pivot").PivotFields("Date").PivotFilters. _
Add Type:=xlDateBetween, Value1:=CLng(Range("DateFrom").Value), Value2:=CLng(Range("DateTo").Value)
回答by Scott
I found your method truncated the dates to days. This might be what you want, for me I wanted to get down to the hour. I kept getting the error:
我发现你的方法将日期截断为天。这可能就是你想要的，对我来说，我想回到小时。我一直收到错误：
Unable to Set Filter
Level_Trend_Date_From = 14/05/2020 22:00:00
Level_Trend_Date_To = 16/05/2020 00:00:00
Error # 1004 was generated by VBAProject
Application-defined or object-defined error
无法设置过滤器
Level_Trend_Date_From = 14/05/2020
22:00:00 Level_Trend_Date_To = 16/05/2020 00:00:00
Error #1004 是由 VBAProject
应用程序定义或对象定义的错误生成的
My fix for this was to just clear the filter before I set it:
我对此的解决方法是在设置之前清除过滤器：
Sub Level_Trend_Set_Dates()
'
' Level_Trend_Set_Dates Macro
' Recorded and tweaked
' Some input from https://stackoverflow.com/questions/22938973/filtering-between-two-dates-in-pivot-table-using-vba-uk-to-us-date-format-issue
'
On Error GoTo ErrorCatch
Level_Trend_Date_From = Range("Level_Trend_Date_From").Value
Level_Trend_Date_To = Range("Level_Trend_Date_To").Value
Level_Trend_Date_From = Format(Level_Trend_Date_From, "DD/MM/YYYY HH:MM")
Level_Trend_Date_To = Format(Level_Trend_Date_To, "DD/MM/YYYY HH:MM")
'If filter is not cleared before being set, always get Error# 1004: Application-defined or object-defined error
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date/Time").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date/Time").PivotFilters.Add _
Type:=xlDateBetween, _
Value1:=Level_Trend_Date_From, _
Value2:=Level_Trend_Date_To
'Value1:=CLng(Level_Trend_Date_From), _
'Value2:=CLng(Level_Trend_Date_To)
GoTo EndSub
ErrorCatch:
ErrorMsg = "Unable to Set Filter" & vbCr
ErrorMsg = ErrorMsg & vbCr & "Level_Trend_Date_From = " & Level_Trend_Date_From
ErrorMsg = ErrorMsg & vbCr & "Level_Trend_Date_To = " & Level_Trend_Date_To
ErrorMsg = ErrorMsg & vbCr & "Error # " & Str(Err.Number)
ErrorMsg = ErrorMsg & vbCr & "was generated by " & Err.Source
ErrorMsg = ErrorMsg & vbCr & Err.Description
MsgBox ErrorMsg, vbCritical, "Level_Trend_Set_Dates - Error", Err.HelpFile, Err.HelpContext
EndSub:
End Sub
Info: UK Date format!
信息：英国日期格式！
