Pivot Table

[Rishabh]

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

 

[Vergel Adriano]

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.

 

[Rishabh]

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]

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

 

[Rishabh]

Hi Vergel,

Thanks allot for timely reponse. It really helped me.

Regards
Sakshi