how connect excel workbook after TPivotGrid.ExportToExcel

[DPiethan]

Dear Alex,

i created differnt charts in runtime, how I can add this to the excel file, or how can I connect the created file?

best regards
Dirk Piethan

Hi,

1. You have to use ExcellApp pointer which passed in TPivotGrid.ExportToExcel and after export performed, youmay add your pictures to Excel like

Sheet := Excel.WorkBooks[1].WorkSheets[2];
Sheet.Cells[0,0] := Picture;

or similar.

Alex

Dear Alex,

thanks for your answer. I would like to test your tipp, but at runtime I get same error like this:

EVariantInvalidOpError mit Meldung 'Ungültige Variantenoperation'.

My code are simple:

procedure TForm6.Button4Click(Sender: TObject);
var
Excel, sheet : OleVariant;
begin
PivotGrid1.ExportToExcel(Excel,True,True,True,'Perfomance Übersicht -'+edt_grp.Text+'-');
sheet:= Excel.WorkBooks[1].WorkSheets[2];
end;

If you have more examples for my problem.

Thanks
Dirk Piethan

[AlexZencovich]

You need to create Excel Application instance before call Export To Excel. Otherwise, if you pass empty ExcelApp to procedure Delphi will automatically clear memory after set counter to ActiveX to 0 on the exit from prcedure where it was initialized.
So you get error because after ExportToExcel you have already invalid pointer to Excel.Application ActiveX. Please look in ExportToExcel source code how to create Excel.Application instance.

[DPiethan]

Hi Alex,

I have found the solution. Though I had tried before already once to create the object, however, got always error.

Thanks and nice weekend
Dirk

Code: Select all

var
   Excel, sheet, newpic : OleVariant;
begin
Excel := CreateOleObject('Excel.Application');
Excel.Visible := True;
PivotGrid1.ExportToExcel(Excel,True,True,True,'Titel -'+edt_grp.Text+'-');
sheet:= Excel.WorkBooks[1].WorkSheets[1];
sheet.name:= 'Pivottabelle';
sheet:= Excel.WorkBooks[1].WorkSheets[2];
sheet.name:= 'DBCHART1';
DBChart1.CopyToClipboardBitmap;
newpic := sheet.Paste;
sheet:= Excel.WorkBooks[1].WorkSheets[3];
sheet.name:= 'DBCHART2;
DBChart2.CopyToClipboardBitmap;
newpic := sheet.Paste;
excel.Workbooks[1].SaveAs('C:\Test.xls');
excel.quit;


[AlexZencovich]

Better if you will add a code

Excel := nil;

at the end of procedure. Just to be clear excel application instance will be destroyed. It have to be by Delphi code, but always better to be sure.