sql-server - 在非常复杂的情况下强制数据透视表刷新

Question

内有详细说明
背景
我有一个带有数据透视表(“PT”)的电子表格，许多用户将通过我公司的门户网站访问该电子表格。假设用户几乎没有使用 Excel 的经验，并且对“附加步骤”几乎没有容忍度。因此，除了正常的枢轴操作之外的用户交互将不起作用。
我正在运行 SQL Server 2008 并使用存储过程来生成数据。我必须使用存储过程，因为我需要能够从 Excel 传递一个参数(这并不理想，但我有很多保护措施)。
因素/解决方案的时间顺序
并发症 1
PT 不能像表等那样直接连接到存储过程。

解决方案这意味着必须首先将数据导入到表中(该表可以连接到存储过程)，然后该表将成为 PT 的源。

并发症 1A
当表刷新(更新数据连接)时，PT 不会自行刷新，因为它没有触发事件。

解决方案 :使用 VBA，特别是表的工作表模块上的工作表更改事件来强制刷新 PT。这很好用，一切都在 100% 工作。

并发症 2
当从远程位置(门户网站)打开位于同一工作簿和此工作簿中的 PT 时，您将收到类似于“错误 1004:找不到源文件”的错误。 PT 尝试使用工作簿的路径连接到其源，但它将临时 URL 视为其路径。

解决方案 : 而不是直接使用 PivotCache.Refresh 刷新 PT , RefreshTable使用 Activeworkbook.RefreshAll当前者给我 Error 1004 时，无论出于何种原因，它实际上确实有效。 .

并发症 3
所以现在我需要检测表中的变化，然后使用 Activeworkbook.RefreshAll .但是尽管使用了 Application.EnableEvents = False甚至设置一个 bool 值以防止更改事件在 Activeworkbook.RefreshAll 时触发被执行，我得到一个无限循环。
工作表模块:

Private blnAbort As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    If blnAbort = True Then Exit Sub
        blnAbort = True
        Call Refresh
        blnAbort = False
    End If
End Sub

标准模块:

Public Sub Refresh()
    Application.EnableEvents = False
    ActiveWorkbook.RefreshAll
    Application.EnableEvents = True
End Sub

导致另一个问题的可能解决方案:
好像是 Activeworkbook.RefreshAll正在执行，然后宏仍在刷新时继续执行。这似乎导致了 Enable_Events事件和 blnAbort及时重置以刷新并触发更改，然后整个事情又重新开始。我更改了数据连接属性 BackgroundRefresh为假。
但是，现在我收到错误 Method 'RefreshAll' of object '_Workbook' failed并且调试只是跳转到 Activeworkbook.RefreshAll这没有帮助。
有任何想法吗？

Answer 1

您如何调用源表的刷新？如果用户通过按钮刷新它，那么您可以强制刷新数据透视表的数据透视缓存...

在工作表代码模块中:

Private Sub BtnRefreshData_Click()
    '// Insert the "refreh" routine or call a procedure to refresh the 
    '// data from the stored procedure here..

    '// Once the data is done being refreshed, manually refresh the pivot cache 
    '// for the pivot table. The code looks like this:

     Sheet1.PivotTables("MyPivotTable").PivotCache.Refresh

    '// If you have a lot of pivot tables to refresh from your "source" data-table
    '// then you can always just do a foreach loop...

     Dim sht1 As Worksheet: Set sht1 = Sheet1
     Dim pTable As PivotTable

     For Each pTable in sht1.PivotTables
         pTable.PivotCache.Refresh
     Next pTable

End Sub

让我知道这是否有帮助，或者更具体地说明为什么像这样直截了当的事情不会起作用，我会尽力再试一次。如果它确实有效，请告诉我!谢谢，布赖恩

更新:

因此，针对您的评论:据我所知，您真正遇到的唯一问题是由交织事件创建的无限循环(刷新数据透视表的事件称为更改事件，刷新数据透视表等) ， ETC...)。我认为此时最简单的解决方案是在刷新事件发生时禁用所有事件，这不应该触发不需要的事件(导致循环)。在刷新数据透视表的代码之前放置以下代码:

    '// Start Code Snippet...

    Application.EnableEvents = False

    '// Do your refresh routine here....

    Application.EnableEvents = True

    '// End Code Snippet

这有帮助吗？如果不是，我会再试一次:) ...谢谢，布赖恩