How can i move excel sheets in the same file

15 visualizaciones (últimos 30 días)
alex
alex el 11 de Sept. de 2019
Comentada: hxen el 2 de Jun. de 2023
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 comentarios
Guillaume
Guillaume el 11 de Sept. de 2019
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
alex el 11 de Sept. de 2019
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!

Iniciar sesión para comentar.

Respuesta aceptada

Guillaume
Guillaume el 11 de Sept. de 2019
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 comentarios
Guillaume
Guillaume el 12 de Sept. de 2019
Editada: Guillaume el 12 de Sept. de 2019
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
alex el 13 de Sept. de 2019
ohhhh!!
Thanks a lot again Guillaume !!
Have to do some studying now to understand what did you do over there!
thanks a lot again!

Iniciar sesión para comentar.

Más respuestas (1)

Image Analyst
Image Analyst el 10 de Sept. de 2020
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.
  1 comentario
hxen
hxen el 2 de Jun. de 2023
awesome! was exactly what I was looking for a work around with writetable. very helpful. :)

Iniciar sesión para comentar.

Categorías

Más información sobre Use COM Objects in MATLAB en Help Center y File Exchange.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by