Pivot cache related--> vbscript code

[Guest]

Hi,

Few weeks back i had posted query related to reading data from excel having
pivot table(reference to the pivot table not know). The solution was:

Sub CreatePivot()
Dim pvtCache As PivotCache
'Dim shtNew As Worksheet

'get the pivot cache object of pivot table in Sheet1
Set pvtCache =
ActiveWorkbook.Worksheets("Sheet1").PivotTables(1).PivotCache
'create a new pivot table in the sheet2 sheet
pvtCache.CreatePivotTable Sheet2.Range("A1").Address(True, True, xlR1C1, _
True), "pivottableX"
'add one data field
With Sheet2.PivotTables(1)
.AddDataField .PivotFields(1), "count", xlCount
End With
End Sub


Problem: I need to perform the whole task using vbscript in ASP.Net code. I
am getting error in last step i.e
With Sheet2.PivotTables(1)
.AddDataField .PivotFields(1), "count", xlCount
End With
Where as the whole code works fine when runas part of macro in excel.

Please help.

Thanks and Regards
Rishabh

 

[papou]

Hi Rishabh
The problem comes from the xlCount argument.
Because, if my memory serves me well, when not executed within Excel, Excel
constants need to be replaced by an integer constant.

Since VBA help shows the argument as optional, try and amend your code
omitting it:

With Sheet2.PivotTables(1)
.AddDataField .PivotFields(1), "count"
End With

HTH
Cordially
Pascal

 

[Guest]

Hi Papou,

Thanks a ton! It worked.
Now i am stuck at next step. After this what i do is click on the cell to
get the complete data. The data is fetched in a new sheet and
programmatically i need to know which sheet it is for further processing.
Code:
Sheet2.Range("B2").ShowDetail = True

Above line of code does the cell (B2) click and fetch the data into a new
sheet.

Thanks and Regards
Rishabh

 

[papou]

Hi Rishabh
Well if you do not know the sheet name you may consider using Activesheet,
provided the sheet involved is the active sheet from the active workbook.

HTH
Cordially
Pascal