Pivot cache related-- vbscript code
 

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

Pivot cache related-- vbscript code
 

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


"Rishabh" a écrit dans le message de
news: ...
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