CODEWITHSUNDEEP
excelmatlabactivex
Quinn Haynie
Quinn Haynie

Reputation: 43

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?

Upvotes: 1

Views: 839

Answers (2)

gnovice
gnovice

Reputation: 125874

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

Upvotes: 1

Quinn Haynie
Quinn Haynie

Reputation: 43

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

Upvotes: 0

Related Questions