MATLAB Answers

alex
0

How can i move excel sheets in the same file

Asked by alex
on 11 Sep 2019
Latest activity Commented on by alex
on 13 Sep 2019
Hello everyone!
I have an Excel file that contains many sheets.
the names of the sheets are something like this:
1_1,1 1_1,2 1_1,3 2_1,3 2_1,2 2_1,1 3_1,1 3_1,2 3_1,3 4_1,3 4_1,2 4_1,1 ....100_1,3 100_1,2 100_1,1
Is there any way to rearrange the sheets so they are in asceding order?
1_1,1 1_1,2 1_1,3 2_1,1 2_1,2 2_1,3 ...... 100_1,1 100_1,2 100_1,3
but if i try to copy all this sheets one by one, create new one ,paste it and then delete it and then move to the next, this would take a long time i believe.
Is there any other solution?
Thank you very much!
P.S: I have the option to rename the sheets like this 1 2 3 6 5 4 7 8 9 12 11 10 .... and then of course i would like them to rearranged as this 1 2 3 4 5 6 7 8 9 10 11 12...

  4 Comments

Show 1 older comment
I have an organ that takes meaurements and it saves them as i mentioned.
I will take same data from every sheet and i will copy them to other sheets and i will proccess them with matlab.
So i thought to rearange the sheets using matlab. Honestly, it didn't cross my mind that i could do this through excel itself.
I will search it of course.
Thank you for the advice!
I have not found any great resource for how to use the actxserver
Matlab documentation on using COM objects is here and Excel's object model documentation is there. The code you'd write in matlab would be more or less identical to the one you'd write in VBA. The main difference is that matlab does not support default properties, so the VBA code:
' wb is an excel workbook
set ws = wb.Worksheets('SomeSheetName') 'Index the worksheets collection. Don't have to use Item as it's the default property
would translate in matlab to:
%wb is an excel workbook
ws = wb.Worksheets.Item('SomeSheetName'); %Item is the default property of the worksheets collection. Has to be called explicitly in matlab
If you're familiar with excel VBA, it's trivial to write the equivalent in matlab. If you're not, you've got a steep learning curve ahead of you.
To move a worksheet, you'd use the Move method of a Worksheet object.
thanks Guillaume for your answer. worksheet.Move is what i need probably.
So i have to do some excel VBA learning now.
And i hope to be able to translate VBA into matlab,or else i will use it directly to excel.
Thank you again!

Sign in to comment.

1 Answer

Answer by Guillaume
on 11 Sep 2019
 Accepted Answer

This should do the job:
function reordersheets(excelfile)
%excelfile: full path of excel file whose sheet are to be reordered
%sheet names must ALL follow the EXACT pattern number_number,number
excel = actxserver('Excel.Application'); %start excel
clearobj = onCleanup(@() excel.Quit); %Quit excel whenever the function exits
workbook = excel.Workbooks.Open(excelfile); %open excel file
sheetnames = arrayfun(@(idx) workbook.Worksheets.Item(idx).Name, 1:workbook.Worksheets.Count, 'UniformOutput', false); %get names of all worksheets
tokens = regexp(sheetnames, '^(\d+)_(\d+),(\d+)$', 'tokens', 'once'); %extract numbers from names
assert(all(~cellfun(@isempty, tokens)), 'At least one sheet name doesn''t conform to pattern');
tokens = str2double(vertcat(tokens{:})); %convert number strings to actual numbers
[~, neworder] = sortrows(tokens, 'descend'); %get new order
ordered = sheetnames(neworder); %and reorder the names accordingly
lastsheet = workbook.Worksheets.Item(ordered{1}); %sheet before which to move current sheet
for sheetname = ordered(2:end)
currentsheet = workbook.Sheets.Item(sheetname{1});
currentsheet.Move(lastsheet); %move sheet before previous sheet
lastsheet = currentsheet;
end
workbook.Save; %save workbook
end

  7 Comments

Actually i have R2014a,and yes i am too many versions behind..
But the problem isn't yet solved because i probably am toooo many versions behind
[~, neworder] = sortrows(tokens, -(1:3)); %get new order
Undefined function or variable 'tokens'.
Anyway,thanks a lot for the help!
I will try learn same excel VBA coding anyway.
Well, obviously the fix replaces the original line that caused the error. So you replace
[~, neworder] = sortrows(tokens, 'descend'); %get new order
by
[~, neworder] = sortrows(tokens, -(1:3)); %get new order
in my original answer.
"Actually i have R2014a". Yes, I see now that the 'descend' option was introduced in R2013b but only fo table inputs. It's only in R2017a that it graduated to a full blown option.
ohhhh!!
Thanks a lot again Guillaume !!
Have to do some studying now to understand what did you do over there!
thanks a lot again!

Sign in to comment.