Editing the format of a chart title in excel through actxserver

I have been trying to work out how to underscore a section of a chart title string in an existing chart in excel through actxserver. I would typically use invoke to format strings in excel cells, but I can't use that approach in chart titles.

In the code below I am replacing a section of the title string, which has a portion already underscored. This automically underscoes the whole title for some reason, which I then remove and try to underscore just the section that was already underscored.

If anyone has a solution it would be much appreciated. I have tried different LLMs, but this is the method they suggest. Which should work based on excel VBA. Possibly this is just not supported in Matlab?

Example of editing characters in a chart title that doesn't work.

excel = actxserver('Excel.Application');
file = excel.Workbooks.Open("ABC.xlsx");
Sheet = excel.Worksheets.Item('ABC');

ChartObjects = Sheet.ChartObjects;
chart = ChartObjects.Item(1).Chart; % Selecting chart

s = chart.ChartTitle.Text; % find current chart title
chart.ChartTitle.Text = strrep(s, OldWord, NewWord); % Replace key word in chart title

chart.ChartTitle.Characters.Font.Underline = 0; % Removing all underscored characters
                
pos = strfind(s, '-'); % finding end of region to underline

% Works up to this last line
chart.ChartTitle.Characters(1, pos(1)-2).Font.Underline = 2; % Underscoring desired region

Example for editing a string in a excel cell that works.

Range = get(Sheet,'Range', 'A1');
Range.Value = {'(Test Area = 21.65 cm2)'};
Chars = invoke(Range,'Characters',22,1); % Selecting character range within cell starting at character 22 and continuing for 1 length
Chars.Font.Superscript = 'true'; % Seting selected character to superscipt

[dpb]

The trick in such cases is to set the debugger to stop at the point of the failing instruction and then explore the Excel object interactively to find the correct syntax to return the object handle and then explore its properties and methods.

It will be possible, but remember you don't have the VBA compiler to translate the VBA syntax of property names to code; it may take a couple of intermediate steps to return addressing objects at the lower level in order to be able to address the properties or methods with dot notation. And, remember that arguments without VBA to identified named arguments are passed positionally and all arguments must be passed up to and including those which may be wanted to be defaulted to the last used one in the list.

I've never messed with the chart object so don't know anything about its details, but anything in VBA is doable with sufficient patience in figuring out syntax to get to the proper object and addressing. These things like substring referencees can be painful because of confusion about the object addressing by subscripting.

An AI-generated rountine's action code line was

With myChart.ChartTitle.Format.TextFrame2.TextRange.Characters(startPos, numChars).Font.UnderlineStyle = msoUnderlineSingle

which appears to be at a different level in the object model than at which you're trying to operate. I didn't go digging into the MS documentation, however...

ADDENDUM

OK, I created a dummy chart...it appears unless it is set somewhere elsewhere, the .Characters property is empty; it is Text that has the string of the title. But, subscripting it returns only the single character of the subscript; not the firs, last positions.

W/O all the cruft to get to the level, the interactive session shows--

>> textRange=(title.Format.TextFrame2.TextRange)
textRange =
	Interface.000C0397_0000_0000_C000_000000000046
>> get(textRange)
        Application: [1×1 Interface.000208D5_0000_0000_C000_000000000046]
            Creator: 1480803660.00
               Text: 'Chart Title'
              Count: 1.00
             Parent: [1×1 Interface.000C0398_0000_0000_C000_000000000046]
         Paragraphs: 0
          Sentences: 0
              Words: 0
         Characters: 0
              Lines: 0
               Runs: 0
    ParagraphFormat: [1×1 Interface.000C0399_0000_0000_C000_000000000046]
               Font: [1×1 Interface.000C039A_0000_0000_C000_000000000046]
             Length: 11.00
              Start: 1.00
          BoundLeft: 'Invoke Error, Dispatch Exception: Unspecified error←↵'
           BoundTop: 'Invoke Error, Dispatch Exception: Unspecified error←↵'
         BoundWidth: 'Invoke Error, Dispatch Exception: Unspecified error←↵'
        BoundHeight: 'Invoke Error, Dispatch Exception: Unspecified error←↵'
         LanguageID: 'msoLanguageIDEnglishUS'
          MathZones: 0
>> textRange.Text
ans =
    'Chart Title'
>> textRange.Text(1,5)
ans =
    't'
>> 

The other way to approach this is to record a macro that does what you want and then figure out how to translate that VBA code into the ActiveX syntax needed to call from MATLAB. It will be possible, it just may take a fair amount of trial and error, but the above clearly doesn't work; looks like you may have to return the whole string, edit it in memory and then rewrite it. The macro code will give you the necessary bread crumbs through the forest, most likely.

[dpb]

Sub Macro1()
    ActiveChart.ChartTitle.Select
    Selection.Format.TextFrame2.TextRange.Characters(1, 6).Font.UnderlineStyle = msoUnderline
    Selection.Format.TextFrame2.TextRange.Characters(1, 6).Font.Bold = msoTrue
    ActiveChart.ChartTitle.Text = "Chart Title"
    Range("G12").Select
End Sub

The above worked interactively to record the macro.

But trying to translate from VBA to callable ActiveX code using

...
chart=wksht.ChartObjects.Item(1).Chart
textRange=chart.ChartTitle.Format.TextFrame2.TextRange
chart.ChartTitle.Characters(1,5)

the last line failed with an out of bounds indexing error.

Not sure what the deal is there, but I don't have time to explore further, sorry.

ADDENDUM:

I wonder if the issue is the workbook is still open but not rewritten...I didn't try a Save and then refresh the workbook handle. Good luck, happy digging through the object model doc's to try to figure it out...

Answer 1

[dpb]

OK, I had a little time to go poking at the Excel doc -- it turns out this is one of those cases where the documentation describes the VBA syntax, but VBA is doing things for you that aren't apparent from the VBA syntax and which, not having the compiler in the process, you have to take on the role yourself...

>> excel=actxGetRunningServer('Excel.Application');
>> fnExcel='C:\Users\...\Documents\MATLAB\Work\Book1.xlsx';
>> wbk=excel.Workbooks.Open(fnExcel);
>> wksht=wbk.ActiveSheet;
>> chart=wksht.ChartObjects(1).Chart;
>> chartTitle=chart.ChartTitle
chartTitle =
	Interface.00020849_0000_0000_C000_000000000046
>> chartTitle.Characters(1,5)
Index in position 2 exceeds array bounds. Index must not exceed 1. 
>>

Well, now we're back where we were and here's where VBA syntax and what you have to do to mimic the same functionality diverge...

Let's see what the 'Characters' property really is --

>> get(chartTitle)
            Application: [1×1 Interface.000208D5_0000_0000_C000_000000000046]
                Creator: 'xlCreatorCode'
                 Parent: [1×1 Interface.000208D6_0000_0000_C000_000000000046]
                   Name: 'Title'
                Caption: 'Chart Title'
             Characters: [1×1 Interface.00020878_0000_0000_C000_000000000046]
    HorizontalAlignment: -4108
                   Left: 144.272440944882
            Orientation: -4128
                 Shadow: 0
                   Text: 'Chart Title'
                    Top: 2
      VerticalAlignment: -4108
           ReadingOrder: -5002
        IncludeInLayout: 1
               Position: 'xlChartElementPositionAutomatic'
                 Format: [1×1 Interface.000244B2_0000_0000_C000_000000000046]
                 Height: 20.09
                  Width: 63.4550393700787
                Formula: 'Chart Title'
            FormulaR1C1: 'Chart Title'
           FormulaLocal: 'Chart Title'
       FormulaR1C1Local: 'Chart Title'
>> 

Aha! It isn't actually the property; it's another object which is why trying to doubly subscript it doesn't work despite VBA code allowing for that. VBA knows what to do there; but don't have it to fix things up for us here...

So, let's see what is in

>> chars=chartTitle.Characters;
>> get(chars)
           Application: [1×1 Interface.000208D5_0000_0000_C000_000000000046]
               Creator: 'xlCreatorCode'
                Parent: [1×1 Interface.00020849_0000_0000_C000_000000000046]
               Caption: 'Chart Title'
                 Count: 11
                  Font: [1×1 Interface.0002084D_0000_0000_C000_000000000046]
                  Text: 'Chart Title'
    PhoneticCharacters: ''
>> chars.Font
ans =
	Interface.0002084D_0000_0000_C000_000000000046
>> get(chars.Font)
      Application: [1×1 Interface.000208D5_0000_0000_C000_000000000046]
          Creator: 'xlCreatorCode'
           Parent: [1×1 Interface.00020878_0000_0000_C000_000000000046]
       Background: -4105
             Bold: 0
            Color: 5855577
       ColorIndex: 56
        FontStyle: 'Regular'
           Italic: 0
             Name: 'Aptos Narrow'
             Size: 14
    Strikethrough: 0
        Subscript: 0
      Superscript: 0
        Underline: -4142
       ThemeColor: 'Invoke Error, Dispatch Exception: Not implemented←↵'
     TintAndShade: 'Invoke Error, Dispatch Exception: Not implemented←↵'
        ThemeFont: 'Invoke Error, Dispatch Exception: Not implemented←↵'
>> 

and, there at the very bottom is are the various font characteristics you're looking for.

You'll need to wrap this up into a neat little function that does the object handle-diving to get down to the actual properties desired.

As noted before, there's always a way, sometimes the road gets pretty twisty and narrow before getting through the woods to Grandmother's house and past the big bad wolf.

[Andrew Conn]

Hello dpb,

Thanks you taking the time to look into this. However, this does not provide a solution to my problem as changing the "Underline" property in "Font" changes it for the whole chart title. I need to be able to change it just for certain characters. E.g. "The relationship between x & y". If you change the underline manually in excel and use "get" to check the properties you just see the value that you last applied, even when there is mixed underscoring in the string.

The properties of a cell range look very similar to the chart title, but for a range you can use "Chars = invoke(Range,'Characters',6,1)" to select a certain character range. But you can't use this appoach for chart titles as you get an error "Error: method or property not found" when running "Chars = invoke(chart.ChartTitle,'Characters',6,1)".

[dpb]

I wondered about that...

I did not try to dig for the internal object the builtin macro referred to

ActiveChart.ChartTitle.Select
Selection.Format.TextFrame2.TextRange.Characters(1, 6).Font.UnderlineStyle = msoUnderline

TextFrame2 as a property under the ChartTitle Format method; it may be there's where the issue is in getting to the .TextRange object. The Excel object model is incredibly complex...

AHA! See the VBA doc for <TextFrame2 Object> and <TextRange2 Object>. One has to go handle-diving even deeper to get to the substrings.

Good luck...it'll undoubtedly take quite a lot of poking at it to finally figure out a calling sequence that will get you there...

I wonder if you could perhaps for a quicker workaround if you can make it work on cell content now, set the text in the cell and then link that cell to the chart title. A little klunky but might just get you the end result wanted while putting off figuring out the clean method syntax issues.

[dpb]

ADDENDUM

It may be the other way would be to record the macro and execute it from MATLAB instead; so far I've not been able to crack the nut directly, either.

I've got other commitments at the moment; later I think I'll try to craft a Q? to post to the MS and reddit Excel forums and see if anybody there will bite...

[Andrew Conn]

Unfortantely even if you set the chart title to equal a formatted cell it doesn't carry across the formating of the string in that cell to the chart title.

[dpb]

I was afraid of that, but worth a try.

I've not had time to probe any further, sorry. It may be using a macro is the only way here.

If it were of vital importance, I think my next step would be to open a debug session in VBA and poke at the objects with VBA and see if could figure out a syntax there that can invoke from the higher level objects handles without the VBA encapsulation in the With clause. If you can get something to work there, then you should be able to move that sequence to COM interface.

The question/potential sticky wicket would be whether even with VBA referencing the object properties and methods without the VBA translation/compilation you still can only get acess to the overall text object rather than being able to get to the Characters() substring. If can't manage that, then it would appear you are, indeed, stuck. Unfortunately, the VBA debug environment is pretty painful to use, but it's about the only other route I can see unless you can post on one of the specific Excel forums and could stumble upon somebody who could help. Unfortunately, while there are a lot of real whizards out there, they all are manipulating with VBA and not many have the handicap of not having VBA and using straight text COM alone.