How can i move excel sheets in the same file

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

I have read this answer by Image Analyst https://www.mathworks.com/matlabcentral/answers/84997-moving-excel-sheets-within-excel-file

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...

[Bob Thompson]

I don't know that you will be able to just rearrange the excel sheets using matlab. Most of matlab's interactions with excel are simply to read or write information, and it doesn't fully categorize the file's contents in an extensive enough manner to capture all of the sheet names. Commands like xlsread and xlswrite are certainly incapable of this.

You may have some luck with the actxserver ability, as this allows you to operate excel remotely with matlab, but I have not found any great resource for how to use the actxserver, and do not know of a way to organize the different sheets based on title alone with it.

Honestly, this sounds like something that would be much easier to do in excel directly, or using VBA code. Is there a reason you are trying not to do that?

[alex]

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!

[Guillaume]

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.

[alex]

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!

Answer 1

[Guillaume]

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

[alex]

Thank you for your interest and your answer Guillaume, but unfortunatelly i got this error,and i am not able to understand and fix the problem.

reordersheets('C:\Users\alex\Documents\MATLAB\ANAFORES\data.xls')
Error using sortrows (line 58)
COL must be numeric.

Error in reordersheets (line 11)
    [~, neworder] = sortrows(tokens, 'descend');   %get new
    order

[Guillaume]

Can you show the content of tokens when the error occurs. Use

dbstop if error

before running the code to get the debugger to stop on error.

[alex]

dbstop if error
>> reordersheets('C:\Users\alex\Documents\MATLAB\ANAFORES\data.xls')
Error using sortrows (line 58)
COL must be numeric.

Error in reordersheets (line 11)
    [~, neworder] = sortrows(tokens, 'descend');   %get new
    order
 
58          error(message('MATLAB:sortrows:COLnotNumeric'));

and in sortrows.m the error is

n = size(x,2);

if nargin < 2
    x_sub = x;
    col = 1:n;
else
    if isnumeric(col)
        col = double(col);
    else
        error(message('MATLAB:sortrows:COLnotNumeric'));   %--HERE IS THE ERROR--
    end
    if ( ~isreal(col) || numel(col) ~= length(col) ||...
            any(floor(col) ~= col) || any(abs(col) > n) || any(col == 0) )
        error(message('MATLAB:sortrows:COLmismatchX'));
    end

    x_sub = x(:, abs(col));
end

[Guillaume]

What I want to see is what's in tokens when the error occurs.

Which version of matlab are you using by the way?

edit: actually I know what the problem is. You're using a very old version of matlab R2013a or earlier which didn't have the 'ascend' / 'descend' option. There's a field at the top right of the page for you to put your version.

[~, neworder] = sortrows(tokens, -(1:3));   %get new order

should fix the problem.

R2019b has just come out, so you're at least 14 versions behind!

[alex]

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.

[Guillaume]

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.

[alex]

ohhhh!!

Thanks a lot again Guillaume !!

Have to do some studying now to understand what did you do over there!

thanks a lot again!

Answer 2

[Image Analyst]

For what it's worth, I have a static method in my Excel_utils class that lets you move a worksheet to be the first one. It's probably easy to modify it to be any index in the workbook:

%--------------------------------------------------------------------------------------------------------------------------------------------------------------------
		% Moves the worksheet named "sheetName" so that it is the very first worksheet in the workbook.
		% Example call:
		% Excel_utils.MoveToSheet1(Excel, 'Summary'); % Make 'Summary' worksheet be the first worksheet in the workbook.
		function MoveToSheet1(Excel, sheetName)
			try
				firstSheet = Excel.Worksheets.Item(1); % Get object/handle of the first worksheet in the workbook.
				currentSheet = Excel.Worksheets.Item(sheetName); % Get object/handle of the user-specified, named worksheet.
				currentSheet.Move(firstSheet); % Move the specified worksheet to be before the first worksheet.
			catch ME
				errorMessage = sprintf('Error in function MoveToSheet1.\nThe Error Message:\n%s', ME.message);
				fprintf(errorMessage);
			end
			return; % from MoveToSheet1
		end % of the MoveToSheet1() method.

[hxen]

awesome! was exactly what I was looking for a work around with writetable. very helpful. :)