Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
Hi,
I have a excel with pivot table but the data source of the pivot table is some other excel which is not given to me. I need to read the original data (i.e. unpivoted data) from it. Is there any way... I have no clue about. Can any one provide me some pointers. Regards Rishabh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
I think this would work. I have XL2003.
1. Drag all fields out of the pivot table. 2. Drag one field as a data item (Drop the field where it says "Drop Data Items Here"). 3. You should now have one row and two columns in the pivot table. with the bottom right cell showing a number. Double click that number. -- Hope that helps. Vergel Adriano "Rishabh" wrote: Hi, I have a excel with pivot table but the data source of the pivot table is some other excel which is not given to me. I need to read the original data (i.e. unpivoted data) from it. Is there any way... I have no clue about. Can any one provide me some pointers. Regards Rishabh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
hi Vergel,
Thanks a lot for your reponse and i never thought any one would reply so quickly. That definetly works but i have to do all that programatically. As the excel i get in my application will change each time and i might not know which cell to double click on. Thanks and Regards Rishabh "Vergel Adriano" wrote: I think this would work. I have XL2003. 1. Drag all fields out of the pivot table. 2. Drag one field as a data item (Drop the field where it says "Drop Data Items Here"). 3. You should now have one row and two columns in the pivot table. with the bottom right cell showing a number. Double click that number. -- Hope that helps. Vergel Adriano "Rishabh" wrote: Hi, I have a excel with pivot table but the data source of the pivot table is some other excel which is not given to me. I need to read the original data (i.e. unpivoted data) from it. Is there any way... I have no clue about. Can any one provide me some pointers. Regards Rishabh |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
Hi Rishabh,
Someone else might have a better idea, but give the code below a try. In the code, my pivot table is on Sheet1 and there is only one pivot table on the sheet. you probably will need to make changes to adapt to your situation.. Sub test() Dim pvtCache As PivotCache Dim shtNew As Worksheet 'get the pivot cache object of pivot table in sheet1 Set pvtCache = Sheet1.PivotTables(1).PivotCache 'create a temporary sheet Set shtNew = ThisWorkbook.Sheets.Add 'create a new pivot table in the temp sheet pvtCache.CreatePivotTable shtNew.Range("A1").Address(True, True, xlR1C1, True), "pivottableX" 'add one data field With shtNew.PivotTables(1) .AddDataField .PivotFields(1), "count", xlCount End With 'Show details (equivalent to double clicking B2) shtNew.Range("B2").ShowDetail = True 'Delete the temp sheet Application.DisplayAlerts = False shtNew.Delete Application.DisplayAlerts = True End Sub -- Hope that helps. Vergel Adriano "Rishabh" wrote: hi Vergel, Thanks a lot for your reponse and i never thought any one would reply so quickly. That definetly works but i have to do all that programatically. As the excel i get in my application will change each time and i might not know which cell to double click on. Thanks and Regards Rishabh "Vergel Adriano" wrote: I think this would work. I have XL2003. 1. Drag all fields out of the pivot table. 2. Drag one field as a data item (Drop the field where it says "Drop Data Items Here"). 3. You should now have one row and two columns in the pivot table. with the bottom right cell showing a number. Double click that number. -- Hope that helps. Vergel Adriano "Rishabh" wrote: Hi, I have a excel with pivot table but the data source of the pivot table is some other excel which is not given to me. I need to read the original data (i.e. unpivoted data) from it. Is there any way... I have no clue about. Can any one provide me some pointers. Regards Rishabh |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
Hi Vergel,
Thanks allot for timely reponse. It really helped me. Regards Sakshi "Vergel Adriano" wrote: Hi Rishabh, Someone else might have a better idea, but give the code below a try. In the code, my pivot table is on Sheet1 and there is only one pivot table on the sheet. you probably will need to make changes to adapt to your situation.. Sub test() Dim pvtCache As PivotCache Dim shtNew As Worksheet 'get the pivot cache object of pivot table in sheet1 Set pvtCache = Sheet1.PivotTables(1).PivotCache 'create a temporary sheet Set shtNew = ThisWorkbook.Sheets.Add 'create a new pivot table in the temp sheet pvtCache.CreatePivotTable shtNew.Range("A1").Address(True, True, xlR1C1, True), "pivottableX" 'add one data field With shtNew.PivotTables(1) .AddDataField .PivotFields(1), "count", xlCount End With 'Show details (equivalent to double clicking B2) shtNew.Range("B2").ShowDetail = True 'Delete the temp sheet Application.DisplayAlerts = False shtNew.Delete Application.DisplayAlerts = True End Sub -- Hope that helps. Vergel Adriano "Rishabh" wrote: hi Vergel, Thanks a lot for your reponse and i never thought any one would reply so quickly. That definetly works but i have to do all that programatically. As the excel i get in my application will change each time and i might not know which cell to double click on. Thanks and Regards Rishabh "Vergel Adriano" wrote: I think this would work. I have XL2003. 1. Drag all fields out of the pivot table. 2. Drag one field as a data item (Drop the field where it says "Drop Data Items Here"). 3. You should now have one row and two columns in the pivot table. with the bottom right cell showing a number. Double click that number. -- Hope that helps. Vergel Adriano "Rishabh" wrote: Hi, I have a excel with pivot table but the data source of the pivot table is some other excel which is not given to me. I need to read the original data (i.e. unpivoted data) from it. Is there any way... I have no clue about. Can any one provide me some pointers. Regards Rishabh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |