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 220.127.116.118
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 firstname.lastname@example.org
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
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?
I'm not sure if Excel files can be uploaded here. You can just email me the Excel file, see my email address above.