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!
如果你有一个简单的解决方案和/或解释这个完全疯狂我会非常感激！
1 个解决方案
#1
6
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:
但是，使用CLng将日期值转换为Longs时，一切正常：
ActiveSheet.PivotTables("Pivot").PivotFields("Date").PivotFilters. _
Add Type:=xlDateBetween, Value1:=CLng(Range("DateFrom").Value), Value2:=CLng(Range("DateTo").Value)
#1
6
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:
但是，使用CLng将日期值转换为Longs时，一切正常：
ActiveSheet.PivotTables("Pivot").PivotFields("Date").PivotFilters. _
Add Type:=xlDateBetween, Value1:=CLng(Range("DateFrom").Value), Value2:=CLng(Range("DateTo").Value)