Menu
We're trying to build a default Excel dashboard which our users can download. When downloading the Excel sheet, we want to populate a sheet in the file with their data.
We're using EPPlus for the manipulation of the Excel sheet.
In short, the structure of the sheet is as follows:
Sheet A with reporting elements (pivot tables, pivot charts and slicers)Sheet B which contains the entire data set=OFFSET(Data!$A$1;0;0;COUNTA(Data!$A:$A);COUNTA(Data!$1:$1)) which simply adapts to the size of the dataset we insert into that sheetSheet A is configured to load data from the Data ModelOverall, this works great as long as we're manually stuffing Sheet B with our data. When we try to use EPPlus for filling in the data we get an error when trying to save the file:
The cachesource is not a worksheet
By trial and error, we've stripped the sheet of it's parts to isolate the cause of the problem. We suspected that it might have been the slicers, the use of Power Query/Data Model or the trick with the named range. However, none of these looks to be the issue - if we remove all Pivot Tables from the sheet then we're able save the Workbook just fine. Surprising to me, we're able to use the Pivot Charts just fine, it's only the tables which are causing the issue.
Any suggestions as to how to avoid this problem with EPPlus? For now, we've continued without the use of Pivot Tables would we would like to have them return at some point :)
836
If you cannot save a workbook when you run Microsoft Excel in Windows Safe mode, the problem may be caused by a third-party add-in or by a file from one of the Excel startup locations. By default, startup files are loaded when you start Excel.
Click Analyze > Refresh, or press Alt+F5. Tip: You can also refresh the PivotTable by right-clicking on the PivotTable, and then selecting Refresh. To update all PivotTables in your workbook at once, click Analyze > Refresh arrow > Refresh All.
Next to the pivot table, click Edit to open the pivot table editor. Add data—Depending on where you want to add data, under Rows, Columns, or Values, click Add. Change row or column names—Double-click a Row or Column name and enter a new name. under Order or Sort by and select the option or item.
First, select and copy the entire pivot table. You can use Control + A to select the whole table. Next, in a new worksheet, use Paste Special, then Values. This will strip away all the formatting and leave you with just the data.
Right-click a cell in the pivot table, and click PivotTable Options. On the Data tab, in the PivotTable Data section, add or remove the check mark from Save Source Data with File. Click OK.
Error - "The cachesource is not a worksheet" can be due to recognition of source data in range by Excel as range in worksheet.
You can change the dumped data in the range into Sheet B as a table, using ws.ListObjects.Add. Whenever pivot table is refreshed, it should automatically takes all the data within the table.
Please also check another alternative available from a related question -Defining a table rather than a range as a PivotTable 'cacheSource', if that helps.
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
