1

I have troubles to read out the font information of an Excel cell containing text of mixed color with Matlab using ActiveX.

Take as an example an excel file with the string "GreenBlueRedBlack" in cell A1 with respective parts of the string in stated color.

MyExcel = actxserver('Excel.Application');
Workbook = MyExcel.Workbooks.Open('D:\data\Test.xlsx');
MySheet  = MyExcel.ActiveWorkBook.Sheets.Item(1);
Text=get(MySheet.Range('A1').Characters,'Text');
Color=MySheet.Range('A1').Characters.Font.Color; % provides NaN

for m=1:size(Text,2) % read out letters seperately
    Color(m)=MySheet.Range('A1').Characters(m,1).Font.Color;
end

The code of course provides NaN when indexing to the whole cell. I am unable to find a way to correctly subindex and loop through each letter in the cell. If I understood correctly Characters(x,y) should be fed with startpoint and length of the wanted subpart of the cell. But Characters(1,1) only returns NaN and Characters(2,1) as well as Characters(1,2) exceeds the matrix dimensions.

How does subindexing to a substring of a cell work? Thank you.

mehmet
  • 1,648
  • 14
  • 21
user2460288
  • 11
  • 2

2 Answers2

0

I found a workaround, maybe somebody can benefit from it. Add following Code into the Module1 of the Excel file.

Public Function getCellInfo(Row As Variant, Col As Variant, Sheet As Variant)
ActiveWorkbook.Sheets(Sheet).Activate
Text = Cells(Col)(Row).Text
TextLength = Len(Cells(Col)(Row))
Dim Color() As Variant
ReDim Color(TextLength)
For m = 0 To TextLength - 1
   Color(m) = Cells(Col)(Row).Characters(m + 1, 1).Font.Color
Next
getCellInfo = Color
End Function

Then call the macro from Matlab using:

ColorVector=MyExcel.Run('getCellInfo',Sheet,Row,Col);

It's not very pretty though. If somebody knows a more elegant way without calling an excel macro that would be awesome.

user2460288
  • 11
  • 2
0

Maybe too late, but this is the solution :

color = MySheet.Range('A1').get('Characters', start, length).Font.Color;
Julien M
  • 1