Changing the name of the sheet in excel during a loop for

hello!

I have a bunch of xlsx files with this names:

20201214_PS0.xlsx

20201214_PS1.xlsx

20201214_PS2.xlsx

20201214_PS3.xlsx

...

I need to change the sheet of each xlsx file with the last part of the name (PS0, PS1, PS2).

I'm trying with xlsheets from Fahad Al Mahmood (2020) https://www.mathworks.com/matlabcentral/fileexchange/4474-xlsheets, MATLAB Central File Exchange. Retrieved December 27, 2020, but I'm not finding the way.

I can locate the part of the name that I need using strfind but I'm not able to put each name in the corresponding sheet and I'm not sure if it is possible.

It would be wonderful if someone can guide me about this question.

If you think that attaching the script may help, I'll do it.

Thank you very much in advance

Merry christmas

Answer 1

[dpb]

Each workbook just has one sheet that is to be named with the trailing sequence of the filename?

That should be pretty simple, if so.

d=dir('20201214*.xlsx');            % get the list of files
for i=1:numel(d)
  xlsheets(extractBetween(d(i).name,'_','.'),d(i).name);
end

If you're trying to put all the sheets into one workbook, this function won't do that, it just names (and adds new ones if needed) the sheets in the workbook; it doesn't write any content to the sheet.

To do that, you'd read each of the files in the above loop and write to a new file with the sheet name--but wouldn't need this function, just use read/writetable or similar functions and specify the sheet name when writing it to begin with.

[Alberto Martínez]

Hello! Thank you for your answer.

Exactly, each workbook has a single sheet that is to be named with the trailing sequence of the filename.

I'm trying with you option but I find the next error:

Error in xlsheets (line 69)

Excel = actxserver('Excel.Application');

Error in Procesado_CTD_SAGUNTO (line 78)

xlsheets(extractBetween(archivo_salida4,'_','.'),d(i).name);

Maybe it's something I'm doing wrong but I don't know how to fix it.

Thank you!

[Image Analyst]

Are you trying to change sheetnames in an existing workbook (if so attach it)?

Or do you want to write out new workbook(s) with a sheetname derived from a list of filenames?

[Alberto Martínez]

d=dir('20201214*.xlsx');            % get the list of files
for i=1:numel(d)
  xlsheets(extractBetween(archivo_salida4,'_','.'),d(i).name);
end

where archivo_salida4 is the output file with the .xlsx

[Image Analyst]

What do you want to do? When you say "I need to change the sheet of each xlsx file with the last part of the name (PS0, PS1, PS2)." what is the change you want to make to the sheet?

[Alberto Martínez]

sorry for the inconvenience, I didn't explain it well.

I need to change the sheet name. I have several xlsx, each file with a single sheet:

20201214_PS0.xlsx

20201214_PS1.xlsx

20201214_PS2.xlsx

20201214_PS3.xlsx

I need to rename each sheet with the last part of the file name.

File 20201214_PS0.xlsx --> rename the sheet with PS0

File 20201214_PS1.xlsx --> rename the sheet with PS1

and so on.

Thank you very much!

[dpb]

That's exactly why my answer would do...but you didn't use the code I gave you.

  xlsheets(extractBetween(archivo_salida4,'_','.'),d(i).name);

Whatever is variable archivo_salida4? It's undefined in the code we can see.

I see I forgot to update the posted code from the temporary variable I used for testing of fnxxx as the filename to be the file under construction, d(i).name. I fixed that in the Answer.

[Alberto Martínez]

Thank you for your kind reply, now works perfect. I replaced fnxxxx for the file name I was using (archivo_salida4) instead of d(i).name and that's the reason of my error.

Now is running!

Thank you very much!

Kind regards.

[dpb]

"I replaced fnxxxx for the file name I was using (archivo_salida4) instead of d(i).name..."

Yeah, my bad. I had a variable in my workspace fn I didn't want to override and wasn't too convenient to build something that dir would return that matched the pattern so I just made up a temporary string to ensure I remembered correctly that extractBetween would return a cellstr which the FEX submission wanted.

The function could have been written to have been more tolerant of other forms of inputs, but was written before the new string class was introduced.

Anyways, I just pasted the line in and forgot to make the edit to make it general...that's what old age does to one...short term memory goes away.

If you want to get somewhat more cryptic,

d=dir('20201214*.xlsx');
arrayfun(@(i) xlsheets(extractBetween(d(i).name,'_','.'),d(i).name),1:numel(d));

or

d=dir('20201214*.xlsx');
cellfun(@(f) xlsheets(extractBetween(f,'_','.'),f),{d.name});

[Alberto Martínez]

thank you very much!

I strongly appreciate your support.

Now that this part is fixed, I'm dealing with the plots. I think that I'm improving each day and I hope I can advance in my script by myself.

Anyway, thank you again to all of you!

Happy new year!

Cheers

Answer 2

[Image Analyst]

In the loop:

% Get name without the extension
[~, baseFileNameNoExt, ext] = fileparts(fileName)
% Get last 3 characters
sheetName = baseFileNameNoExt(end - 2 : end); 
xlswrite(xlFullFileName, Data, sheetName, 'A1');

OR

% Get name without the extension
[~, baseFileNameNoExt, ext] = fileparts(fileName)
% Take what's after the last underline.
underLines = find(baseFileNameNoExt == '_');
sheetName = baseFileNameNoExt(underLines(end) + 1 : end);
xlswrite(xlFullFileName, Data, sheetName, 'A1');

[Image Analyst]

OK there are several ways to do this. Do you know if there is an ActiveX function to rename a sheet? If so use it. You can find out what it is by recording a macro and looking at the macro's code. Alternatively you can use my attached class to duplicate a sheet with a new name, and then delete the old one.

[Alberto Martínez]

thank you very much! I finally achieved it using xlsheets with the guides provided by dpb.

Best wishes!