如何通过单元格引用更新powerpivot数据透视表过滤器？-Howtoupdatepowerpivotpivottablefilterviacellreference?

I am working on updating a Powerpivot pivot table via a cell reference in a different worksheet but am having trouble with determining the correct syntax.

我正在通过不同工作表中的单元格引用更新Powerpivot数据透视表，但在确定正确的语法时遇到问题。

The code works just fine if I hard-code a date (see below):

如果我硬编码日期，代码就可以正常工作（见下文）：

Sheets("Close Rate").Select                   'Select the sheet containing the pivot table to update

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Closed Cases].[Closed Date Week End].[Closed Date Week End]").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Closed Cases].[Closed Date Week End].[Closed Date Week End]"). _
    CurrentPageName = _
    "[Closed Cases].[Closed Date Week End].&[2013-09-28T00:00:00]"

However, if I try to use a variable rather than hard-code a date, I get an "Application Defined or Object Defined error" message.

但是，如果我尝试使用变量而不是硬编码日期，我会收到“应用程序定义或对象定义错误”消息。

This is the code I'm trying to use:

这是我正在尝试使用的代码：

'Set up variables
Dim FilterDate As String

FilterDate = Sheets("CS Dashboard").Range("I5").Value   'Get date for filter

Sheets("Close Rate").Select                   'Select the sheet containing the pivot table to update

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Closed Cases].[Closed Date Week End].[Closed Date Week End]").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Closed Cases].[Closed Date Week End].[Closed Date Week End]"). _
    CurrentPageName = _
    "[Closed Cases].[Closed Date Week End].&[FilterDate]"

Can anyone give some guidance on how I should code this so that it uses the variable?

任何人都可以提供一些指导我应该如何编码，以便它使用变量？

1 个解决方案

"[Closed Cases].[Closed Date Week End].&[" & FilterDate & "]"