Delphi - Changing Chart Title in Excel causes AV -

Question

I am using Delphi Seattle to build and display a Chart in Excel (2013). I build my chart from a pivot table. The chart displays everything fine. It has a default title, so I want to change that. This should be a simple property change, but I keep getting AV errors. When I google, the closest thing I can find mentions that I need to select the Chart and/or Chart title before I change it. [UPDATED] Here is a working sample that shows the problem.

procedure TForm1.Button1Click(Sender: TObject);
var
  oExcel : ExcelApplication;
  RawDataSheet :_Worksheet;
  myChart: Shape;
begin
    oExcel := CreateOleObject('Excel.Application') as ExcelApplication;
    oExcel.Visible[LOCALE_USER_DEFAULT] := True;

   // Add a New Workbook, with a single sheet
   oExcel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   // Get the handle to the active Sheet, and insert some dummy data
   RawDataSheet :=  oExcel.ActiveSheet as _Worksheet;
   RawDataSheet.Range['A1', 'B10'].value2 := 10;

    // Now add my chart
    myChart := RawDataSheet.Shapes.AddChart2(208, xlColumnClustered, 200, 10,  300, 300, True);

    // Try to access the Chart Title...  This always AVs here.
    myChart.Chart.HasTitle[LOCALE_USER_DEFAULT] := True;

    // Set Title Text.  If Comemnt out above line, this AVs as well
    myChart.Chart.ChartTitle[LOCALE_USER_DEFAULT].Caption := 'New Chart Title';

end;

I cannot find any way to change my Title. I can't even find any way to READ the existing title, let alone change it.

The Microsoft Object Model documentation for ChartTitle says that this is the correct property...(https://msdn.microsoft.com/en-us/library/office/ff840521.aspx)

Additional Info: I did generate my own type library, Excel_TLB, and I have that in my USES clause. My complete USES clause is...

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, ComObj, Excel_TLB;

Answer 1

I was able to set a title as below. The difference is to select the chart to be able to refer to it through ActiveChart. Note that I have office 14 which doesn't have AddChart2, so I used AddChart.

uses
  comobj, excel_tlb;

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  oExcel : ExcelApplication;
  RawDataSheet :_Worksheet;
  myChart: Shape;
begin
    oExcel := CreateOleObject('Excel.Application') as ExcelApplication;
    oExcel.Visible[LOCALE_USER_DEFAULT] := True;

   // Add a New Workbook, with a single sheet
   oExcel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   // Get the handle to the active Sheet, and insert some dummy data
   RawDataSheet :=  oExcel.ActiveSheet as _Worksheet;
   RawDataSheet.Range['A1', 'B10'].value2 := 10;

    // Now add my chart
//    myChart := RawDataSheet.Shapes.AddChart2(208, xlColumnClustered, 200, 10,  300, 300, True);
    myChart := RawDataSheet.Shapes.AddChart(xlColumnClustered, 200, 10,  300, 300);
    myChart.Select(False);

    oExcel.ActiveChart.HasTitle[LOCALE_USER_DEFAULT] := True;
    oExcel.ActiveChart.ChartTitle[LOCALE_USER_DEFAULT].Caption := 'New Chart Title';
end;

Also note, I don't know the implication of the parameter that's passed to Select.

Another option that works is to let go of type safety after some point (where it doesn't work anymore) and rely on VBA examples found on MS documentation (which does not match signatures with that of generated type library). This allowed to work on the chart directly.

procedure TForm1.Button1Click(Sender: TObject);
var
  oExcel : ExcelApplication;
  RawDataSheet :_Worksheet;
  myChart: Shape;
  V: OleVariant;
begin
    oExcel := CreateOleObject('Excel.Application') as ExcelApplication;
    oExcel.Visible[LOCALE_USER_DEFAULT] := True;

   // Add a New Workbook, with a single sheet
   oExcel.Workbooks.Add(EmptyParam, LOCALE_USER_DEFAULT);
   // Get the handle to the active Sheet, and insert some dummy data
   RawDataSheet :=  oExcel.ActiveSheet as _Worksheet;
   RawDataSheet.Range['A1', 'B10'].value2 := 10;

    // Now add my chart
//    myChart := RawDataSheet.Shapes.AddChart2(208, xlColumnClustered, 200, 10,  300, 300, True);
    myChart := RawDataSheet.Shapes.AddChart(xlColumnClustered, 200, 10,  300, 300);

    V := myChart.Chart;
    V.HasTitle := True;
    V.ChartTitle.Caption := 'Chart Title';
end;