Hi,
Clear the protection? I don’t know what your refering to.
Hi,
There is nothing I’m doing to create that Protected View. I’ve never seen it before and can’t reproduce it in Excel 2007, even when I open the file attached above. It must be a feature within Excel 2010. I have reattached the original file if that helps.
Hi,
It was created manually from nothing more than a new (blank) Excel workbook.
As an alternative to setting the pivot tables to refresh on open I’ve tried to update all pivot tables via code just before the save line like this:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
For Each xlsWorksheet In xlsWorkbook.Worksheets
For Each xlsPivotTable In xlsWorksheet.PivotTables
xlsPivotTable.RefreshData()
xlsPivotTable.CalculateData()
Next
Next
My problem here is I’m getting this error at the RefreshData method:
Error: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
I can’t figure out what the Index parameter is referring to and I’m having trouble creating sample code to recreate the error.
Hi,
Ok, while creating a sample project for you I discovered the object causing the problem. My pivot table has a calculated field in it. On click of the button you get an error “Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index” thrown at the xlsPivotTable.RefreshData() line. If you go into the Excel template file and remove the calculated field from the pivot table, all is fine and dandy. Can you find a solution for this problem?
Hi,
Hi,
Hi,
At present, we could not calculate the data if the pivottable has calculatedfield. We will support it later.
Please try this sample code.
C#
xlsPivotTable.RefreshData();
xlsPivotTable.RefreshDataOnOpeningFile = true;
// xlsPivotTable.CalculateData();
Well that’s very disappointing. Without supporting calculated fields that just puts more work back on IT (me) to create any calculation an end user may need in the database since they cannot create them their self in the spreadsheet.
Setting RefreshDataOnOpeningFile = True just brings me back to the original problem I had when I started this thread. Microsoft’s overprotective security alert disables the pivot table refresh and someone reading the report will not select the enable option. I need to rethink my entire approach to this project now.
Hi,
I have added your comment in the related issue. We will update you asap.
The issues you have found earlier (filed as 29876) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
I’m having this same problem. Can you please provide the code that refreshes the piviot tables?
Hi,
Thanks for using Aspose.Cells for .NET.
Please download and use the latest version:
Aspose.Cells
for .NET v7.3.0.5 and use the following code to refresh the pivot tables.
C#
pivotTable.RefreshData();
Let us know if you still face any issue, we will help you asap.
Actually I do need a hand. I’ll start a new thread.
Hi,
Thanks for your posting.
For your new issues, please create new threads with your sample code, source xls/xlsx files and screenshots.
It will help us to sort out your issue quickly.