xlsread with changing Excel name

Hello all,

I was helped here before, so i'm hoping that it's possible to repeat that.

My situation:

I have a GUI that will read a few (about 20) m-files into the base workspace. This works fine, but: Every m-file is using xlsread as follows:

[num, tekst, raw] = xlsread('Data rev. 9.xlsx','Workbook','B9:C138');

Every time, it is the same excel file, but with different worksheets. Now my problem: As you can see, this is rev. 9. This excel file is changing a lot. And then, it is named rev 10, rev 11, rev 12 etc. So every time, I have to adjust this in 20 files. That is not a lot of work, but if i have to do it everytime, it is not a good solution.

The GUI file is always in the same directory as the excel file.

My question: Is it possible to write a code or something that will automatically take the excel file, regardless of the filename?

Thank you in advance!

Regard, Bart

Answer 1

[Iain]

Step 1: Get your list of files:

 dir_struct = dir('*.xlsx'); % (with or without a path, as needed.)

Step 2: Get the revision number:

 for i = 1:numel(dir_struct)
  number(i) = str2double(dir_struct(i).name(10:(10+numel(dir_struct(i).name)-16))); % get the number-string out of the filename and turn it into a number (I have fixed the format there)
 end

Step 3: Sort the numbers, and get the order:

 [revised_numbers, order] = sort(number);

Step 4: Read and use

 for i = 1:numel(order)
  [.... ] = xlsread(dir_struct(order(i)).name, ...);
 ... rest of code
 end

[Bart]

Thank you!! This works fine untill now.

Also thanks to the rest of you!

Answer 2

[Azzi Abdelmalek]

for k=1:10
  filename=sprintf('rev%d.xls',k);
  [num, tekst, raw] = xlsread(filename,'Workbook','B9:C138');
  % additional code
end

[Bart]

Also thank you for your answer.

If I put it into my code like this:

for k=1:100 %to 100 because it is at 9 already
  filename=sprintf('data rev. %d.xlsx',k);
  [num, tekst, raw] = xlsread(filename,'Workbook','B9:C138');
 end

I now get the following error:

Error using xlsread (line 129)
XLSREAD unable to open file 'data rev. 1.xlsx'.

So it is starting with 1, but it has to look further to find (at the moment) 9.

Thanks!

[Azzi Abdelmalek]

Then use

for k=9:100

Answer 3

[David Sanchez]

You can try to adjust the following to your code:

my_xls = dir ('*.xlsx'); % struct with your xlsx files

for k = 1:length(my_xls)
    xls_file = my_xls(k).name; % string with name of k-th xlsx file name
end

[Bart]

Thank you for your fast answer.

I understand what you are doing, but how do I put this in my code?

I think I have to adjust some of the excel names to the excel names I'm using? But I can't figure out which one:

So if I have the following:

my_xls = dir ('*.xlsx'); % struct with your xlsx files

for k = 1:length(my_xls)
    xls_file = my_xls(k).name; % string with name of k-th xlsx file name
end

[num, tekst, raw] = xlsread('xls_file','Workbook','B9:C138');

Do I have to change "dir" to the directory with the xlsx file? And do I have to change the .name to something else?

Sorry, but I don't have a lot of MATLAB experience, so this is all new for me. The name of my excel file is 'data rev. 9.xlsx' (10, 11 etc).

Thanks again for helping me!