Databases
StackOverflow2013
Postsdbo
POForcing a Pivot table to refresh in a very complicated scenario
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POForcing a Pivot table to refresh in a very complicated scenario
    primarykey
    data
    text
    <p><strong>Detailed description inside</strong></p> <h2>Background</h2> <p>I have a spreadsheet with a Pivot table ("PT") that many users will access through my company's web portal. The users are assumed to have little to no experience with Excel and little to no tolerance for "additional steps". So user interaction apart from normal pivot operation won't work.</p> <p>I am running SQL Server 2008 and using a stored procedure to generate the data. I have to use a stored procedure because I need to be able to pass along a parameter from Excel (this is not ideal but I have numerous safeguards).</p> <h2>Chronological Order of Factors/Solutions</h2> <p><strong>Complication 1</strong> </p> <p>A PT cannot directly connect to a stored procedure like it can tables etc. </p> <ul> <li><strong>Solution</strong> This means that the data must first be imported into a table (which can connect to a stored procedure) and then this table will be the source for the PT.</li> </ul> <p><strong>Complication 1A</strong></p> <p>When the table refreshes (updates data connection) the PT will not refresh itself because it has no trigger event.</p> <ul> <li><strong>Solution</strong>: Use VBA and specifically the worksheet change event on the table's sheet module to force refreshes on PT. This works great and all was working 100%.</li> </ul> <p><strong>Complication 2</strong></p> <p>When a PT whose is within the same workbook and this workbook is opened from a remote location (web portal), you will get an error that looks like "Error 1004: Source file not found". The PT tries to use the path of the workbook to connect to its source but it sees the temporary URL as its path.</p> <ul> <li><strong>Solution</strong>: Instead of directly refreshing the PT using <code>PivotCache.Refresh</code>, <code>RefreshTable</code> use <code>Activeworkbook.RefreshAll</code> which for whatever reason does actually work when the former gave me the <code>Error 1004</code>. </li> </ul> <p><strong>Complication 3</strong></p> <p>So now I need to now detect a change in the table and then use <code>Activeworkbook.RefreshAll</code>. But despite using <code>Application.EnableEvents = False</code> and even setting a boolean to prevent the change event from kicking in when the <code>Activeworkbook.RefreshAll</code> is executed, I am getting an infinite loop.</p> <p><em>Worksheet Module:</em></p> <pre><code>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 </code></pre> <p><em>Standard Module:</em></p> <pre><code>Public Sub Refresh() Application.EnableEvents = False ActiveWorkbook.RefreshAll Application.EnableEvents = True End Sub </code></pre> <p><strong>Possible Solution that caused another problem:</strong></p> <p>It seems like the <code>Activeworkbook.RefreshAll</code> was being executed and then the macro just continued on while it was still refreshing. This seemed to be causing the <code>Enable_Events</code> event and <code>blnAbort</code> to be reset just in time for something to refresh and trigger a change and the whole thing started again. I changed the data connection property <code>BackgroundRefresh</code> to false.</p> <p>However, now I am getting the error <code>Method 'RefreshAll' of object '_Workbook' failed</code> and the debug is just jumping to <code>Activeworkbook.RefreshAll</code> which is not helpful.</p> <p>Any ideas?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTyler
    1. USTyler
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POForcing a Pivot table to refresh in a very complicated scenario
      1. USGriGrim
      1. VTFavorite
    2. VO
      singulars
      1. POForcing a Pivot table to refresh in a very complicated scenario
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POForcing a Pivot table to refresh in a very complicated scenario
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload