Trying to retrieve IDataLabel interface to specific text box on graph

[Martel_David]

I am hoping there is a simple solution to this that I have just not found.

I have a workbook that I use as a template. In that I have a chart sheet. On the chart sheet is one (or possibly more text boxes). These are linked to a cell on another datasheet.

I am making a copy of the data sheet, and the chart for each sample. The reference to the data sheet obviously does not get updated, so I have to go find the text box, and tweak the cell reference. I have done this succcessfully for the chart title, and the axis labels, since I have a simple call to get to the IDataLabel interface.

The only way I have found (and this is where maybe I am missing something) to get a grip of the text box is the following code (in the template the text box has been edited for its Alternate Text to be 'Figure Caption')

  for j:= 1 to Xls.ObjectCount do
  begin
    ShpProp:= Xls.GetObjectProperties(j, true);
    if SameText(ShpProp.GetAlternateText, 'Figure Caption') then
    begin
      //Do something
      idxObj:= j;
      break;
    end;
  end;

This works well. I can find the AutoShape. If I was not using a cell reference I could even set the text directly using the following

    Xls.SetObjectText(idxObj, '', 'This is the next text for the box');  

But the text box is a reference and the text does not take that way, and I would really prefer to set it through the data sheet. In order to do that I need to get an IDataLabel interface.

Here is where I am stuck. I can't work out how to search while returning IDataLabel directly. I have also not found any method on the interface, or a casting that I can use to get to it.

Any help greatfully received.

[adrian]

Hi,
There is some mixed stuff here: The chart title, labels, etc, are "Data labels" and so that's why they implement the IDataLabel. (and they have their own way to link with cells in the sheet). The other thing you have here is a TextBox, which can be linked to a cell by writing say "=A1" in the formula bar when the box is selected, but that is a completely different thing. TextBoxes are autoshapes, not data labels, so they implement their own interfaces.

We have a Xls.SetObjectLinkedText, but as you might have find out, that won't work either. This is because this is for "real" objects, like a button or a label (the objects in the "Forms" palette in the developer tab). As you can expect those objects have a completely different mechanism to link text from both a chart data label or an autoshape (a textbox is an autoshape)

We currently don't have a Xls.SetShapeLinkedText, but it shouldn't be difficult to implement, since we already do all the work when loading xlsx files with linked text. I'll see if it can be added to the 7.10 release

[adrian]

For what it is worth, the method SetShapeLinkedText was added and seems to be working fine. Tomorrow we'll finish writing some more tests and adding it to APIMate. If everything goes well 7.10 should be released this week (near the end)

[Martel_David]

Adrian,

I totally understand that you are constrained by the design of the Microsoft (open?) file format for xlsx files. It does not surprise me that they have many different ways of doing what (on the surface) seems like the same thing. The background on the technicalities was interesting and useful.

What is even more useful is your responsiveness and willingness to get these quirks dealt with in the new release. I am very grateful, and am now even more excited to get the next update.