create excel pivot with ole
lazarus / 34-Windows

----------------------------------------------------------------------
nicola69 · 2013-06-17 16:17

Hi
My lazarus version is 1.0.10 , fpc 2.6.2 , cpu icore 5 and my so is
windows xp 32 bit.

I need to write an application that create pivot on excel.
I have problem with this procedure

Code: [Select]procedure tform1.creazionepivotsottoconto(var
XLSPivotCosti :olevariant);
var
  PivotCache : OleVariant;
begin
   PivotCache :=
XLSPivotCosti.ActiveWorkbook.PivotCaches.Create(1,'Sottoconto
Contabile!R1C1:R2630C16',3);
    XLSPivotCosti.WorkSheets[3].Select;
   XLSPivotCosti.ActiveSheet.Name := 'PivotSottoconto';
   PivotCache.CreatePivotTable('PivotSottoconto!R3C1','Pivot
Sottoconto',,3);
   XLSPivotCosti.ActiveSheet.PivotTables('Pivot
Sottoconto').PivotFields('Compagnia').Orientation := 3;
   XLSPivotCosti.ActiveSheet.PivotTables('Pivot
Sottoconto').AddDataField(
XLSPivotCosti.ActiveSheet.PivotTables('Pivot
Sottoconto').PivotFields('Importo'),'Somma di importo',-4157)

end;                                                                 
                                    

when i call PivotCaches.Create(1,'Sottoconto
Contabile!R1C1:R2630C16',3) i receive this message :  'Toleexception
parameter is incorrect

my problem is first parameter . I need pass as enum type as
XlPivotTableSourceType
In delphi there is a tool a Import Type library that resolve this
problem.
In delphi there is a type called ToleEnum


There is a workaround ?

Can someone help me ?

----------------------------------------------------------------------
ludob · 2013-06-17 16:44

TOleEnum is just a LongInt. Nothing special.

See here for the values for XlPivotTableSourceType
http://msdn.microsoft.com/en-us/library/bb241435(v=office.12).aspx

Your sheet name contains a space and quoting is needed. See
http://answers.microsoft.com/en-us/office/forum/office_2010-
customize/activeworkbookpivotcachescreate/089692ba-711f-405a-9a35-
40a6e4e12903

----------------------------------------------------------------------
nicola69 · 2013-06-18 07:46

hi ludob

Thank you for your advice.

I have changed name to sheet without space.
I have changed first statement in

Code: [Select]  PivotCache := XLSPivotCosti.ActiveWorkbook.PivotCaches
.Create(1,'SottocontoContabile!R1C1:R2630C16',3);
When i debug this statement i receive this message error OleException
: parameter is incorrect.

I have attached screenshot of my error. It 's in italian language

Parameter is correct because i want emulate this vba statement:

Code:
[Select]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,
SourceData:= _
        "Sottoconto Contabile!R1C1:R2630C16",
Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Foglio4!R3C1", TableName:=
_
        "Tabella_pivot1", DefaultVersion:=xlPivotTableVersion12

xlDatabase has  value 1.

I have tried this procedure but the error is same:

Code: [Select]Procedure tform1.creazionepivotsottoconto(var
XLSPivotCosti :olevariant);
 const
   xlDatabase = $00000001;
var
  PivotCache : OleVariant;
begin
   // PivotCache := XLSPivotCosti.ActiveWorkbook.PivotCaches.Create(1,
'SottocontoContabile!R1C1:R2630C16',3);
  PivotCache := XLSPivotCosti.ActiveWorkbook.PivotCaches.Create(Source
Type:=xlDatabase);
  XLSPivotCosti.WorkSheets[3].Select;
   XLSPivotCosti.ActiveSheet.Name := 'PivotSottoconto';
   PivotCache.CreatePivotTable('PivotSottoconto!R3C1','Pivot
Sottoconto',,3);
   XLSPivotCosti.ActiveSheet.PivotTables('Pivot
Sottoconto').PivotFields('Compagnia').Orientation := 3;
   XLSPivotCosti.ActiveSheet.PivotTables('Pivot
Sottoconto').AddDataField( XLSPivotCosti.ActiveSheet.PivotTables('Pivo
tSottoconto').PivotFields('Importo'),'Somma di importo',-4157)
end;                                      

In microsoft documentation only first parameter is required.

http://msdn.microsoft.com/en-us/library/bb238847(v=office.12).aspx

----------------------------------------------------------------------
BigChimp · 2013-06-18 07:54

OT/Hint: instead of making your cold bold, you can use the code tags
(see the button marked #) to automatically format and display the
code...

----------------------------------------------------------------------
nicola69 · 2013-06-18 08:03

i have changed to code tag