在从SQL Server DB更新的单独工作表中的数据之后自动刷新Excel 2007数据透视表

Background:

背景：

I have three sheets in Excel.

我在Excel中有三张纸。

A summary sheet with a pivot table that pulls data from #2 below
带有数据透视表的摘要表，可从下面的＃2中提取数据
A data sheet that pulls data from a SQL Server DB. (Pulls a category type and a dollar value)
从SQL Server数据库中提取数据的数据表。（拉出类别类型和美元值）
A sheet that has a button to refresh both the data and ideally the pivot table.
一张工作表，其中包含用于刷新数据和理想数据透视表的按钮。

Problem: When I click on the button, the data sheet refreshes correctly but the pivot table doesn't. This workbook automatically runs at 5am and sends the results to people as a PDF, so I have to figure out a way to have that pivot table refresh before the PDF is generated, sent, and the workbook closed.

问题：当我单击按钮时，数据表正确刷新但数据表没有。此工作簿自动在凌晨5点运行，并以PDF格式将结果发送给人员，因此我必须找到一种方法，在生成，发送PDF和关闭工作簿之前刷新数据透视表。

What I have tried: First, when the button is clicked it runs:

我尝试过：首先，当点击按钮时，它会运行：

ActiveWorkbook.RefreshAll
Application.CalculateFull

It does update the data sheet correctly, just not the pivot.

它确实更新了数据表，而不是数据表。

I have tried:

我努力了：

Adding the following commands between the two commands above and after both to no avail:
在上面和之后的两个命令之间添加以下命令无效：

Sheets("Summary").PivotTables("PivotTable6").PivotCache.Refresh
Sheets("Summary").PivotTables("PivotTable6").RefreshTable

I have tried running both of those commands twice (back to back) like this:
我尝试过两次（背对背）运行这两个命令，如下所示：

ActiveWorkbook.RefreshAll
Application.CalculateFull
ActiveWorkbook.RefreshAll
Application.CalculateFull

In the hopes that it would get data in the first run, and have a successful pivot refresh in the second. Didn't work.

希望它能在第一次运行中获得数据，并在第二次运行中成功进行数据透视刷新。没工作。

I have tried to trick Excel into thinking I closed and reopened the workbook and then reran the refreshes:
我试图欺骗Excel认为我关闭并重新打开工作簿然后重新刷新：

 ThisWorkbook.Saved = True
 Workbooks(1).Activate
 ActiveWorkbook.RefreshAll
 Application.CalculateFull

The reason I tried this is because after running the workbook (pivot not refreshing) I save and close. I reopen (pivot is still wrong, data is right), I rerun, (data is right again and the same) and now the pivot is correct. So I was hoping to simulate this.

我尝试这个的原因是因为在运行工作簿（数据库没有刷新）后，我保存并关闭。我重新打开（枢轴仍然是错误的，数据是正确的），我重新运行，（数据再次正确并且相同）现在枢轴是正确的。所以我希望能够模拟这个。

At this point I can't think of anything else. I am at the point where I don't think Excel can do this. One more thing. Originally we had the data coming directly into the pivot table from SQL Server DB, but we continually got these errors so we were going to take a different approach:

在这一点上，我想不出别的什么。我不认为Excel可以做到这一点。还有一件事。最初我们将数据从SQL Server DB直接进入数据透视表，但我们不断得到这些错误，因此我们将采取不同的方法：

Removed Part: /xl/pivotTables/pivotTable1.xml part. (PivotTable view)
删除部分：/xl/pivotTables/pivotTable1.xml部分。（数据透视表视图）
Removed Part: /xl/pivotTables/pivotTable5.xml part. (PivotTable view)
删除部分：/xl/pivotTables/pivotTable5.xml部分。（数据透视表视图）
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
删除记录：/xl/workbook.xml部分（工作簿）中的工作簿属性

 <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
     <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <logFileName>error014800_01.xml</logFileName> 
        <summary>Errors were detected in file 'T:\Reports\RP\Archive\Historical Excel\01-11\RP_01-07-11.xlsm'</summary> 

        <removedParts summary="Following is a list of removed parts:">
            <removedPart>Removed Part: /xl/pivotTables/pivotTable1.xml part. (PivotTable view)</removedPart> 
            <removedPart>Removed Part: /xl/pivotTables/pivotTable5.xml part. (PivotTable view)</removedPart> 
        </removedParts>

        <removedRecords summary="Following is a list of removed records:">
           <removedRecord>Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)</removedRecord> 
        </removedRecords>
     </recoveryLog>

Any help is greatly appreciated.

任何帮助是极大的赞赏。

3 个解决方案

#1

You can use the worksheet changed event to trigger a refresh on your pivot table.

您可以使用工作表更改事件来触发数据透视表上的刷新。

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrHandler
    Application.EnableEvents = False

    'Check to see if the worksheet range raising this event overlaps the range occupied by our data dump
    If (Not (Intersect(Target, ActiveSheet.ListObjects("DATA_TABLE_NAME_HERE").Range) Is Nothing)) Then

        'If it does, then refesh the pivot tables that depend on this data (not automatic, name each table pivot table explicity)
        ActiveSheet.PivotTables("PIVOT_TABLE_NAME_HERE").RefreshTable

    End If

ErrHandler:
    Application.EnableEvents = True
End Sub

You might need to replace ActiveSheet with Sheets("whatever") depending on how your workbook frames up.

您可能需要使用表格（“任何”）替换ActiveSheet，具体取决于工作簿的构建方式。

#2

I've been in the same situation and I found out it was due to the enabling of Background query on the connection.

我一直处于相同的情况，我发现这是由于在连接上启用了背景查询。

I include the following in most of my sheets with this configuration in order to force the settings to disallow background querying.

我使用此配置在大多数工作表中包含以下内容，以强制设置禁止后台查询。

Sub SetNoBackgroundQuery()
      Dim i As Integer
      Dim j As Integer
      i = ThisWorkbook.Connections.Count
      If i = 0 Then End
      For j = 1 To i
        ThisWorkbook.Connections(j).ODBCConnection.BackgroundQuery = False
    '    Debug.Print ThisWorkbook.Connections(j).Name

  Next j
End Sub

#3

I don't think that this is a good idea- you really should be using Analysis Services in order to refresh the data.

我不认为这是一个好主意 - 你真的应该使用Analysis Services来刷新数据。

How large is the database / query?

数据库/查询有多大？

I've used Excel pivot tables against 5tb databases and it always gives sub-second results. Because I used Analysis Services.

我已经使用Excel数据透视表对5tb数据库，它总是给出亚秒结果。因为我使用过Analysis Services。

#1