actxserver copy and paste iteratively

Hi everyone,

The question relates to the use of the use of the actxserver for an excel application.

I'm creating a a standalone to compare two excel tables. The Tables are nearly equal. Every week I'm getting a new excel table. I have to check if there are rows that were cleared.

The actual problem is in the second part of the code but I wanted to show you also the first part to help you to understand what I'm trying to do.

%#########################
%##      FIRST PART     ##
%#########################

function [] = exc_1_call (varargin)
[S.filename_xlsx_1, S.pathname_xlsx_1] = uigetfile({'*.xlsx'},'');
file = strcat(S.pathname_xlsx_1, S.filename_xlsx_1);

[S.num_1,S.txt_1,S.raw_1] = xlsread(file);   % first spreadsheet

S.excel_1 = actxserver('Excel.Application');   % open Excel Server
S.excel_1.Visible = true;    % make excel visible

S.workbook_1 = S.excel_1.Workbooks.Open(file); %open excel file

S.worksheet_1 = S.workbook_1.Worksheets.Item(1);   %get worksheet reference

% the next part is giving me the color index for every cell in column 'M'
% I need to do it because the color of the cells will be compared
N = length(S.raw_1);
all_i = 6:N;
S.ColorIndex = zeros(size(all_i));

for k = 1:numel(all_i)          
 i = all_i(k);
 m = strcat('M',num2str(i));
 S.ColorIndex_1(k) = S.worksheet_1.Range(m).Interior.ColorIndex;
 S.ColorIndex_1 = transpose(S.ColorIndex_1);       % in my case it's a 1x5301 double
end

eSheet = S.excel_1.ActiveWorkbook.Sheets;           % adding a new sheet
eSheet1 = Item(eSheet,1);
eNewSheet = Add(eSheet,[],eSheet1);
S.workbook_1.Worksheets.Item(2).Name = 'Sheet_2';

end

function [] = exc_2_call (varargin)
[S.filename_xlsx_2, S.pathname_xlsx_2] = uigetfile({'*.xlsx'},'');
file = strcat(S.pathname_xlsx_2, S.filename_xlsx_2);

[S.num_2,S.txt_2,S.raw_2] = xlsread(file);   % first spreadsheet

S.excel_2 = actxserver('Excel.Application');   % open Excel Server
S.excel_2.Visible = true;    % make excel visible

S.workbook_2 = S.excel_2.Workbooks.Open(file); %open excel file

S.worksheet_2 = S.workbook_2.Worksheets.Item(1);   %get worksheet reference

N = length(S.raw_2);
all_i = 6:N;
S.ColorIndex_2 = zeros(size(all_i));

for k = 1:numel(all_i)          
 i = all_i(k);
 m = strcat('M',num2str(i));
 S.ColorIndex_2(k) = S.worksheet_2.Range(m).Interior.ColorIndex;
 S.ColorIndex_2 = transpose(S.ColorIndex_2);
end
end

Now we're coming to the part where I can't find a solution.

%#########################
%##     SECOND PART     ##
%#########################

function [] = df_call (varargin)

S.ID_1 = S.raw_1(6:end,12);     % that's a certain column from table 1 with information that have to be compared
S.ID_2 = S.raw_2(6:end,12);     % same column from the second excel table

S.ID_find = ismember(S.ID_1, S.ID_2);           % here I'm checking if there are missing cells in table 2 
S.ID_find = double(S.ID_find);                  % convert to double because S.ID_find was 'logical'
[ID_row_zeros] = find(S.ID_find == 0);          % looking for zeros (missing row)

ID_row_zeros_str = string(num2str(ID_row_zeros));   % convert to string to concatenate strings
ID_deleted_cells = strcat('M', ID_row_zeros_str);   % concatenate strings

%  copy the first six rows from the first table (first sheet) and paste it to the first excel table NEW SECOND sheet
S.worksheet_1.Rows.Item(1).Copy;
S.workbook_1.Worksheets.Item(2).Range('A1').PasteSpecial(13);
S.worksheet_1.Rows.Item(2).Copy;
S.workbook_1.Worksheets.Item(2).Range('A2').PasteSpecial(13);
S.worksheet_1.Rows.Item(3).Copy;
S.workbook_1.Worksheets.Item(2).Range('A3').PasteSpecial(13);
S.worksheet_1.Rows.Item(4).Copy;
S.workbook_1.Worksheets.Item(2).Range('A4').PasteSpecial(13);
S.worksheet_1.Rows.Item(5).Copy;
S.workbook_1.Worksheets.Item(2).Range('A5').PasteSpecial(13);
%
% NOW HERE COMES THE "PROBLEM"
% here I want to copy the missing rows and paste it to the NEW SECOND sheet of the first table
% for example:  ID_row_zeros contains three missing rows. that means i have ID_row_zeros (3x1 double) or ID_row_zeros_str (3x1 string) 

S.worksheet_1.Rows.Item(ID_row_zeros(1,1)).Copy;                % that works fine. I'm copying the first missing row  
S.workbook_1.Worksheets.Item(2).Range('A6').PasteSpecial(13);   % and paste it to the 2. sheet to 'A6'

S.worksheet_1.Rows.Item(ID_row_zeros(2,1)).Copy;                % copy second missing row 
S.workbook_1.Worksheets.Item(2).Range('A7').PasteSpecial(13);   % and paste it to the 2. sheet to 'A7'

S.worksheet_1.Rows.Item(ID_row_zeros(3,1)).Copy;                % copy third missing row 
S.workbook_1.Worksheets.Item(2).Range('A8').PasteSpecial(13);   % and paste it to the 2. sheet to 'A8'

The thing is that I have to do it iteratively because there could be missing more than just three rows. I never know how many rows will be missing. So somehow I have to copy and paste it depending on the number of missing rows.

I hope I gave enough information to understand my problem. It's not that easy to explain such a problem. If you need some more information please let me know.

Any help/direction would be most appreciated.

Thanks in advance.

[Guillaume]

Your second part function cannot possibly run. S.raw_1 is undefined on the first line.

Anyway, rather than some code, it would be much better to tell give a more complete description of what you want to copy where and what the conditions are. Example files would be useful.

A few comments about your code:

• function [] = functionname(..) is the same as function functionname(..)
• All your functions have varargin as input but never use it. Might as well have function functionname()
• Use fullfile instead of strcat to build paths.
• The use of length on something that is very likely to be a 2D array makes your code fragile. Use size with the proper dimension.
• You're transposing S.colorindex in a loop while you're filling it. S.colorindex will end up as a column vector for an odd number of iterations and as a row vector for an even number of iteration. I assume that's a bug. In any case, why not create it as a vector in the correct direction to start with.
• esheet1 will be the same reference as s_worksheet1 (unless the user clicked on another workbook while the code is running, completely breaking your code). Using Activexxx is not a good idea, particularly if you make excel visible
• The reason why some variables are stored as field of the structure S while others are just normal variables is unclear.

[Christian Hermann]

Thanks for your reply.

Reading your comments I realize how bad my matlab skills are -.-'

But first of all I'm asking myself why S.raw_1 is undefined. I thought that I stored it in a handle called "S" to call raw_1 in a different function. I wanted to give you some examples files to give you more information about my original problem but first I have to solve this one. It's not my first GUI with different functions and I can't remember having problems with passing values between functions.

I tried to work with setappdata but I dind't manage it.

[Guillaume]

The only way S.raw_1 can be defined inside your df_call function is if df_call is a nested function, which is probably not a good design for you. Variables defined in other functions are never visible to normal functions, so at the beginning of df_call there is only one variable that exists: varargin.

You say that you're working in a GUI. Is this GUIDE, app designer, or fully coded on your own? Also are the functions callbacks and if so, how is the callback created?

[Christian Hermann]

I fully coded on my own. I've modified my code to have the permission to upload my m-file here so you can maybe check the code quickly.

Wouldn't you recommend to code on my own?

I've uploaded the m-file and two excel tables. The tables are almost the same. In table_2 there are missing about four rows which I would like to find in df_call.

[Guillaume]

I haven't got the time to look at this in depth right now, I'll come back to it probably tonight. A few things:

"Wouldn't you recommend to code on my own?" That's entirely up to you. You don't seem to be doing to bad with building the GUI from scratch but you may want to look at the App designer which may make it easier for you. However, note that the App designer is OOP so very different from the way you've developped your GUI. Your GUI is more similar to what GUIDE would create but I wouldn't recommend using GUIDE. It's been deprecated now.

One important thing, when you define a callback with:

.. = uicontrol(.., 'Callback', {@somecallback, handles});

the variable that is passed to the callback is the handles in the exact same state as it is at the moment you create the callback. Subsequent changes/additions to that variable won't affect that variable. The {} creates a cell array whose content is a function handle and a copy of the handles.

One way to fix this is to indeed use guidata. So define the callbacks as:

.. = uicontrol(.., 'Callback', @somecallback);

at the end of the GUI creation:

guidata(handles.fh, handles);  %store the handles in the figure

and in your callbacks

function somecallback(hobject, eventdata)
    handles = guidata(hobject);  %get the latest version of handles
    %...
    guidata(hobject, handles);  %update the handles
end

edit: Oh, just noticed that your callback functions are nested functions (indenting them would have made this clearer) so this changes the scoping rules a bit. Ironically, if you hadn't passed at all the handles to the callback, your code would work properly and you wouldn't need guidata either. In the editor, handles would turn blue to indicate that the variable spans functions scope. So that's another option, define the callback as:

.. = uicontrol(.., 'Callback', @somecallback);

and the callback function

function somecallback(hobject, eventdata)  %nested function, so has access to variables of the main function
    %use handles variable as is
    handles = dosomethingwith(handles);  %uses the same handles as the main function.
end

[Guillaume]

Christian Hermann's comment mistakenly posted as an answer moved here:

Hi again,

so now I'm able to use my GUI and all the functions. I will to upload the m-file and tables that will work. The first tables were wrong. Maybe you can test it.

But I still couldn't figure it out how to copy and paste iteratively.

I will summarize the problem again.

I want to compare two tables that should be equal. But there could miss some rows in the 2nd sheet. I have to check which rows are missing. I know how to find the missing rows. This works fine. I want to copy the missing rows, paste them into the new created 2nd sheet and save it as a new excel-table. As I said this works. But just for the rows that I define. Like in the code down below --> (ID_row_zeros(1,1))

%##########   copy and paste deleted rows to the 2nd sheet    ##############

handles.worksheet_1.Rows.Item(ID_row_zeros(1,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range('A6').PasteSpecial(13);

handles.worksheet_1.Rows.Item(ID_row_zeros(2,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range('A7').PasteSpecial(13);

handles.worksheet_1.Rows.Item(ID_row_zeros(3,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range('A8').PasteSpecial(13);

As you can see I define three rows that have to be copied to the second sheet from 'A6' to 'A8'.

But what if ID_row_zeros contains 400 rows that are missing in the second table. I have to copy and paste them iteratively to the second sheet.

Answer 1

[Guillaume]

I'm afraid it's been a while so i don't remember the whole discussion.

It sounds like you want to merge two tables which have some common columns and may have some rows missing from either. If so, I wouldn't do that with Excel (although it's certainly possible). I'd use something like this:

%importing the excel files into matlab using MODERN techniques:
opts = detectImportOptions('table_1_1.xlsx', 'VariableNamesRange', '5:5', 'TextType', 'string');
t1 = readtable('table_1_1.xlsx', opts);
opts = detectImportOptions('table_2_2.xlsx', 'VariableNamesRange', '5:5', 'TextType', 'string');
t2 = readtable('table_2_2.xlsx', opts);

%replace missing strings by "" so they're considered equal by the join (<missing> values are never equal)
t1 = fillmissing(t1, 'constant', "", 'DataVariables', @isstring);
t2 = fillmissing(t2, 'constant', "", 'DataVariables', @isstring);

%join the two tables, merging identical rows and keeping missing rows from both table (full outer join)
merged = outerjoin(t1, t2, 'MergeKeys', true, 'Keys', 1:12)

The above use columns 1:12 of both tables as keys for the merge whereas your original code just used column 12. It makes more sense to me to use all the columns, but there's a lot of non-identical values in columns 1:11 for the same ID in column 12 (eg ID 123_554_747 has Info6 as yes in one file but no in the other). If you just used column 12:

merged = outerjoin(t1, t2, 'MergeKeys', true, 'Keys', 'ID')

I'm not sure how you reconcile the differences between the mismatches in table 1 and 2.

In any case, you can just export the merged table to a new excel file with writetable.

[Christian Hermann]

Thanks again for your reply.

I don't want to merge the tables. I want to compare them and find out if there are missing rows.

I've simplified the tables to make it easier to understand it. It was stupid that I've didn't it before. The tables are equal except that in the second table are missing four rows. I've colored the rows purple in the first table.

So I want to find the missing ID (for example 123_554_751) in the second table and put the whole row of the missing ID to the new created sheet.

And I want to do it this way because the new (third) table has to have the same layout like the other ones.

And I have the feeling that I'm very close finding the solution.

I don't know if your code above helps me to do this. To find it out I have to understand the commands that you`ve used because I don't know them.

But again....thanks a lot for your help.

[Guillaume]

It might actually be better to have the actual format of the table to make sure that the code I give is applicable. With your new demo tables, this is how to find which rows from the first table are not in the 2nd table:

%imports the two tables
opts = detectImportOptions('table_1_1_1.xlsx', 'VariableNamesRange', '5:5', 'TextType', 'string');
t1 = readtable('table_1_1_1.xlsx', opts);
opts = detectImportOptions('table_2_2_2.xlsx', 'VariableNamesRange', '5:5', 'TextType', 'string');
t2 = readtable('table_2_2_2.xlsx', opts);

%finding which rows of t1 are not in t2 (using just the first two columns):
missingdata = t1(~ismember(t1(:, [1, 2]), t2))

You can then write that new table to a new excel file. If the formatting of the file is important write it in a preformated template file (or do the formatting via actxserver).

[Christian Hermann]

It's also important to do it using actxserver because later on I will have to change the cell color using if-conditions.

e.g.

If both tables contain ID 123_554_751 I have to check if Info1 (column A) didn't change. If it didn't change than I have to copy from the first table the colored cell and the text that is in it and paste it to the new sheet as well. If Info1 has changed than I have to change the cell color and the text in the new sheet.

That's just an example to let you know why it's so important for me to to it this way

[Christian Hermann]

If I use the following code I get an error which I don't understand :/

opts = detectImportOptions('table_1_1_1.xlsx', 'VariableNamesRange', '5:5', 'TextType', 'string');

Error using detectImportOptions

'VariableNamesRange' is not a recognized parameter. For a list of valid name-value pair arguments, see the documentation for detectImportOptions.

Error in detectImportOptions>getTypedParser/parsefcn (line 352)

p.parse(args{:});

Error in detectImportOptions>spreadsheetArgs (line 376)

args = parser(otherArgs);

Error in detectImportOptions (line 270)

args = spreadsheetArgs(p.Unmatched);

[Christian Hermann]

And you're right. It's a good idea to use a preformated template file. I already did something like that when I used Matlab to create a PowerPoint presentation. I didn't create the whole presentation with matlab. I just filled a template

[Guillaume]

"I use the following code I get an error which I don't understand"

I completely missed that you're using 2016b, which is the first version with detectImportOptions. It was a lot less developed than it is now and didn't yet have the 'VariableNamesRange' option.

As workaround you may be able to use the 'Range' with a much larger range than you'll ever need. The aim of this is to tell detectImportOptions to pick row 5 to names the variables (in my version it picks row 4 if left to work it out). So, try:

opts = detectImportOptions('table_1_1_1.xlsx', 'Range', '5:10000');  %assuming your files will always have less than 10,000 rows

This works in my version (2020a). No idea how it behaves in 2016b. There's been a lot of improvements to the detection capabilities in recent years.

edit: I realise that I haven't answered your question, which is how to copy multiple rows at once.

It's exactly as you have done. Just create a range that covers the rows you want to copy.

excel = actxserver('Excel.Application');
wbsource = excel.Workbooks.Open(fullfile(pwd, 'table_1_1_1.xlsx'));
wbdest = excel.Workbooks.Add;
wssource = wbsource.Sheets.Item(1);
wsdest = wbdest.Sheets.Item(1);
rgsource = wssource.Range('A4:C5,A13:C13,A27:C27,A35:C36');
rgdest = wsdest.Range('A1');
rgsource.Copy;
rgdest.PasteSpecial(13);  %13 => xlPasteAllUsingSourceTheme
excel.Selection.Clear;
excel.Visible = true;

[Christian Hermann]

Does this really work for you?

rgsource = wssource.Range('A4:C5,A13:C13,A27:C27,A35:C36');

I can't define a range like I would like to do. I already tried it out to define a range like 'A5:A9' but than I get an error

Error using Interface.000208D8_0000_0000_C000_000000000046/Range

Error: Object returned error code: 0x800A03EC

I just can do this:

rgsource = wssource.Range('A4');

[Guillaume]

Yes, I tested the code before posting it.

Make sure that the workbook is not in preview mode (you may want to make excel visible from the start). It could also be due to your version of excel, I'm on Office 365. In any case, the problem is on the excel side. The range is perfectly valid.

To avoid these sorts of issue, I would really recommend you follow my initial answer of using matlab to do the processing and simply writing into a preformatted sheet.

[Christian Hermann]

I don't know why but your code doesn't work for me.

"To avoid these sorts of issue, I would really recommend you follow my initial answer of using matlab to do the processing and simply writing into a preformatted sheet."

You're right...I should try to do this but as I said I also have to check the color of the cells using this code:

N = length(handles.raw_1);
all_i = 6:N;
handles.ColorIndex = zeros(size(all_i));

for k = 1:numel(all_i)          
 i = all_i(k);
 m = strcat('M',num2str(i));
 handles.ColorIndex_1(k) = handles.worksheet_1.Range(m).Interior.ColorIndex;
 handles.ColorIndex_1 = transpose(handles.ColorIndex_1);
end

But there is another option. I can also color the cells depending on the text which is inside of the cells. Maybe I can compare the rows and find the missing cells. Then I write this in a preformatted sheet. And in the next step I color the cells using the following code

S.workbook_1.Worksheets.Item(1).Range(S.cell).Interior.ColorIndex = 3;

[Christian Hermann]

In case someone will have the same problem I've found a solution.

 for k = 1:handles.lenght_ID_row_zeros
handles.worksheet_1.Rows.Item(ID_row_zeros(k,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range(['A', num2str(k+5)]).PasteSpecial(13);
 end

I can paste it using a for-loop. I know...it's a very special case but maybe there will be someone else who will have the same problem.

And thanks again Guillaume for all your help and energy.