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

Question

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?

gnovice · Accepted Answer · 2019-04-17 17:30:59Z

1

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);

answered Apr 17, 2019 at 17:30

gnovice

126k16 gold badges259 silver badges364 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quinn Haynie · Accepted Answer · 2019-04-17 14:28:40Z

0

The solution to this is of course, a for loop.

   alphacolumn=char(97:117);

% iterate through data array

for i=1:21
        str=string(alphacolumn(i))+2;
        str2=string(alphacolumn(i))+202;
        write1=char(str+":"+str2);
   if ~isreal(tsc{i,1})
        T = (tsc{i,1});
for j = 1:length(T)
        rrange = xl.ActiveWorkbook.Activesheet.Range(char(string(alphacolumn(i)) + string(j+1)));
            xlcompatiblestring1 = char(string(T(j,:,:)));
    rrange.Value= xlcompatiblestring1;

end
   else
        tsci=tsc{i,1};            
% write data to xl target file
        %xlswrite(xlfilepath,tsci,write1);
        xlActivesheetRange = get(xl.Activesheet,'Range',write1);
        xlActivesheetRange.Value = tsci;
   end
end

answered Apr 17, 2019 at 14:28

Quinn Haynie

438 bronze badges

1 Comment

Quinn Haynie Over a year ago

Hopefully this will help someone who's stuck. Please comment if you know a better way!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related