When Exporting Oracle table to Excel appending a worksheet, the existing worksheet pivot table is corrupted. Conditional formatting is gone and when I refresh the Pivot table all the columns with spaces (non-standard Oracle columns) in my Pivot Data Source table disappeared from the Pivot Table field list. I'm using Toad for Oracle 13.2.0.258
I found out the Excel Pivot Data Source table is using a formula created via the Name Manager under Excel's Formula tab, which is used by the Pivot table for data (worksheet "Detailed Data") vs an Excel named table. I use this in case the number of data rows changes. Anyway this formula is corrupted once I append a worksheet from Toad for Oracle Export.
Formula: =OFFSET('Detailed Data'!$A$1,0,0,COUNTA('Detailed Data'!$A:$A),COUNTA('Detailed Data'!$1:$1))
Can you work up a test case for me? Send me an excel file and steps to reproduce it with one of the SCOTT tables or something in the data dictionary? Send to john.dorlon@quest.com
Thanks
Hi, tried duplicating the problem I had with the SCOTT tables and determined it is when I refresh data after switching between table and range of the new exported file. It switched Okay but it didn't refresh and corrupted the =OFFSET command in the Excel Name Manager and the Pivot. Tried without exporting and the same thing happened. Looks like a user problem 
Hi John,
I duplicated the problem with sample data. The Excel file is attached. I exported an Oracle table into tab "Ora_Table". I then created a defined name under Formula->Name Manager under Define Name group, called it PivotData with refers to: =OFFSET(Ora_Table!$A$1,0,0,COUNTA(Ora_Table!$A:$A),COUNTA(Ora_Table!$1:$1)). I then inserted a Pivot table using the "PivotData" as my table/range.
I use the formula range to add, delete rows with exported data without having to resize or rebuild a table.
I successfully added/deleted rows and refreshed the pivot without issue. Once I exported a table to another tab eventually wanting to replace the Ora_Table with new rows, it stops working. Without replacing the Ora_Table, just doing a refresh after export the file, the =OFFSET gets corrupted.
Attached is the Excel which works fine. Once you export anything to another new tab, try the refresh of the pivot table and the =Offset in the define name PivotData gets corrupted.
Thanks for looking into this.
I can't seem to upload the excel file as .xlsx, only images and text. how do I allow Excel files to upload?
John
Hi John,
I'm not sure if Excel files can be uploaded here. You can just email me the Excel file, see my email address above.
Thanks
John