Auto Refresh Excel 2007 Pivot Table after data in separate sheet updated from SQL Server DB

Background:

I have three sheets in Excel.

1. A su开发者_如何学Cmmary 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)
3. 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.

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:

 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:

• Removed Part: /xl/pivotTables/pivotTable1.xml part. (PivotTable view)
• Removed Part: /xl/pivotTables/pivotTable5.xml part. (PivotTable view)
• Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

 <?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.

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.

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