>Hello all
>
>#define xlAverage -4106
>
>oPivotTable.PivotFields("avgsal").function = xlAverage fires a oleexception error
>
>Is there any other value for xlAverage ?
>
>txs

Akhan,
I think this is related with your earlier thread and you're not referring to correct item. My guess is that your pivotfield would be :

oPivotTable.PivotFields("Sum of avgsal").function = xlAverage

Below code extracted from one of my working routines might help :

Function DoCreatePivots
Dimension aPivotTables[2,4]
aPivotTables[1,1] = "Number of Something" && Sheet name
aPivotTables[1,2] = "nField"		  && Field to use in calc
aPivotTables[1,3] = xlSum	          && Formula to use in calc
aPivotTables[1,4] = "#,##0"	    && Number format to use in pivot table

aPivotTables[2,1] = "Another Sheet"
aPivotTables[2,2] = "nField2"
aPivotTables[2,3] = xlAverage
aPivotTables[2,4] = "#,##0.0"

* Excel pivot tables could hold more than one field for row, column, page
* VBA array is like a VFP array so use arrays instead of static varnames
* You might want to pass more than one field for one of these
* ie: taPageFields[2] and values "PageField1", "PageField2"
* would make PageField2 a page field

local array taRowFields[1], taColumnFields[1], taPageFields[1]   

taRowFields[1] = "RowField"
taColumnFields[1] = "ColField"
taPageFields[1] = "PageField1"

=_Pivot2Excel("PivotData","R1C1:R"+lcRows+"C"+lcCols, ;
  @taRowFields, @taColumnFields, @taPageFields, @aPivotTables)

Function _Pivot2Excel
Lparameters tcDatabaseSheetName, tcDataRange,;
  taRowFields, taColumnFields, taPageFields, taPivotTables
* Pivot all pivottables passed by array
With oExcel.ActiveWorkBook
  For ix = 1 To Alen(taPivotTables,1)
    =_CreatePivot(tcDatabaseSheetName, tcDataRange, ;
     @taPivotTables, @taRowFields, @taColumnFields, @taPageFields)
  Endfor
Endwith

Function _CreatePivot
Lparameters tcDatabaseSheetName, tcDataRange, taPivotTables, ;
 taRowFields, taColumnFields, taPageFields
Wait Window Nowait "Creating pivot table " + taPivotTables[ix,1]
.Sheets(tcDatabaseSheetName).Activate
.ActiveSheet.PivotTableWizard(xlDatabase, ;
  tcDatabaseSheetName+"!"+tcDataRange,;
  "", taPivotTables[ix,1]) && Wizard sets Range and name
With .ActiveSheet.PivotTables(taPivotTables[ix,1])
  If !Empty(taPageFields[1]) && Fields added
    .AddFields( @taRowFields, @taColumnFields, @taPageFields ) 
  Else
    .AddFields( @taRowFields, @taColumnFields )
  Endif
* tcDataField set as data field
  .PivotFields(taPivotTables[ix,2]).Orientation = xlDataField 
  If taPivotTables[ix,3] # xlSum && If not sum (default) change
    .PivotFields("Sum of "+taPivotTables[ix,2]).Function = ;
          taPivotTables[ix,3] && Calculation method set
  Endif
Endwith
With .ActiveSheet
  .Name = taPivotTables[ix,1]
  .PivotTables(taPivotTables[ix,1]).PivotSelect( "", xlDataAndLabel)
  .Range("A1").AutoFormat(xlRangeAutoFormatColor2)
  If Type("taPivotTables[ix,4]") = "C" And !Empty(taPivotTables[ix,4])
    oExcel.Application.Selection.NumberFormat = taPivotTables[ix,4]
  Endif
  .Range("A1").Select
Endwith

Cetin