Excel – 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 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)
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.