Search code examples
excelmatlabactivex

Using MATLAB to write String values into Excel spreadsheet via ActiveX protocol.. found a problem

I'm using MATLAB 2017a and have been using xlswrite in the past to perform this operation. The problem I ran into was with execution speed and I was looking for a better way. So, I decided to use actxserver and write data using get(obj) from MATLAB and Range.Value from ActiveX. Here's what the code looks like:

e = actxserver('Excel.Application);
eWorkbook = e.Workbooks.Add;
e.Visible = 1;
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = eSheets.get('Item',1);
eSheet1.Activate;
A = ["Str1";"Str2";"Str3";];
eActivesheetRange = get(e.Activesheet, 'Range', 'A1:A3');
eActivesheetRange.Value = A;

This inocuous bit of code does not execute, nor does it throw a warning or error message.. Nothin'. In my mind, the eActivesheetRange evaluates to: Range("A1:A3") on the ActiveX side. Interestingly, if I replace

A = ["Str1";"Str2";"Str3";];

with

A = char(["Str1";"Str2";"Str3";]);

then the program writes the A char array to each cell in the eActivesheetRange Range.

Is there a way to call cells() using the MATLAB Range.Value connection? Would cells().Value be able to solve this problem?

Solution

  • I don't think writing to Excel using ActiveX is able to handle string types properly. In this case, you can make it work by simply converting your string array into a cell array of character vectors using cellstr. Changing your last line of code to the following works for me (in R2016b):

    eActivesheetRange.Value = cellstr(A);

    Replacing the last two lines with the following also works:

    e.Activesheet.Range('A1:A3').Value = cellstr(A);