title: Auto Refresh Excel 2007 Pivot Table after data in separate sheet updated from SQL Server DB
body: <p><strong>Background:</strong></p>
  
  <p>I have three sheets in Excel. </p>
  
  <ol>
  <li>A summary sheet with a pivot table that pulls data from #2 below</li>
  <li>A data sheet that pulls data from a SQL Server DB.  (Pulls a category type and a dollar value)</li>
  <li>A sheet that has a button to refresh both the data and ideally the pivot table.</li>
  </ol>
  
  <p><strong>Problem:</strong>
  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.</p>
  
  <p><strong>What I have tried:</strong>
  First, when the button is clicked it runs:</p>
  
  <pre class="lang-vb prettyprint-override"><code>ActiveWorkbook.RefreshAll
  Application.CalculateFull
  </code></pre>
  
  <p>It does update the data sheet correctly, just not the pivot.</p>
  
  <p>I have tried:</p>
  
  <ul>
  <li>Adding the following commands between the two commands above and after both to no avail:</li>
  </ul>
  
  <pre class="lang-vb prettyprint-override"><code>Sheets("Summary").PivotTables("PivotTable6").PivotCache.Refresh
  Sheets("Summary").PivotTables("PivotTable6").RefreshTable
  </code></pre>
  
  <ul>
  <li>I have tried running both of those commands twice (back to back) like this:</li>
  </ul>
  
  <pre class="lang-vb prettyprint-override"><code>ActiveWorkbook.RefreshAll
  Application.CalculateFull
  ActiveWorkbook.RefreshAll
  Application.CalculateFull
  </code></pre>
  
  <p>In the hopes that it would get data in the first run, and have a successful pivot refresh in the second.  Didn't work.</p>
  
  <ul>
  <li>I have tried to trick Excel into thinking I closed and reopened the workbook and then reran the refreshes:</li>
  </ul>
  
  <pre class="lang-vb prettyprint-override"><code> ThisWorkbook.Saved = True
   Workbooks(1).Activate
   ActiveWorkbook.RefreshAll
   Application.CalculateFull
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <ul>
  <li>Removed Part: <code>/xl/pivotTables/pivotTable1.xml</code> part.  (PivotTable view)</li>
  <li>Removed Part: <code>/xl/pivotTables/pivotTable5.xml</code> part.  (PivotTable view)</li>
  <li>Removed Records: Workbook properties from <code>/xl/workbook.xml</code> part (Workbook)</li>
  </ul>
  
  
  
  <pre class="lang-xml prettyprint-override"><code> &lt;?xml version="1.0" encoding="UTF-8" standalone="yes" ?&gt; 
       &lt;recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"&gt;
          &lt;logFileName&gt;error014800_01.xml&lt;/logFileName&gt; 
          &lt;summary&gt;Errors were detected in file 'T:\Reports\RP\Archive\Historical Excel\01-11\RP_01-07-11.xlsm'&lt;/summary&gt; 
  
          &lt;removedParts summary="Following is a list of removed parts:"&gt;
              &lt;removedPart&gt;Removed Part: /xl/pivotTables/pivotTable1.xml part. (PivotTable view)&lt;/removedPart&gt; 
              &lt;removedPart&gt;Removed Part: /xl/pivotTables/pivotTable5.xml part. (PivotTable view)&lt;/removedPart&gt; 
          &lt;/removedParts&gt;
  
          &lt;removedRecords summary="Following is a list of removed records:"&gt;
             &lt;removedRecord&gt;Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)&lt;/removedRecord&gt; 
          &lt;/removedRecords&gt;
       &lt;/recoveryLog&gt;
  </code></pre>
  
  <p>Any help is greatly appreciated.</p>