Actxserver reading only first file

working with this code-

get_files = cellstr(get(handles.listboxfiles,'String'));
len = length(get_files);     

for i = 1 : len                    
    excel = actxserver('Excel.Application');            
    workbook = excel.Workbooks.Open(get_files{i});     
    .....
    .....
    .....
    .....
    .....    
    .....
end

Am not getting any error but Actxserver reads only first file from list. Listboxfiles contains name of the files with pathname. Something like this "C:\Users\ERTW\Documents\Excel_file\ARAN1.xlsm". Is something missing with excel.Workbooks.Open?

[Guillaume]

Note that your question has nothing to do with actxserver. actxserver is used to connect matlab to any COM/ActiveX component. It can be excel but it can be anything else such as other Office programs (Word, Access, Outlook, etc.), matlab itself, or anything that support scripting.

actxserver does not read files.

Answer 1

[Guillaume]

Have you verified that get_files actually contains more than one element. Is your listbox multiselect?

There is nothing wrong with your Workbooks.Open call and you'd get an error if excel failed to open the workbook. Of course, you overwrite the workbook variable on each step of the loop, so if you expected an array of workbooks after the loop is finished, you're not going to get one.

Note that your code starts a new instance of excel at each step of the loop. That's very inefficient. You would be better off with:

excel = actxserver('Excel.Application'); %start excel
%excel.Visible = true;   %uncomment for debugging
for fidx = 1:numel(get_files)     
  workbook = excel.Workbooks.Open(get_files{i});     
  %...
  workbook.Close();
end
excel.Quit();  %Don't forget to quit each excel instance or they'll be left running in the background invisible

The best way for you to figure out what is going on is to make excel visible and see how your code affects the excel window.

[Ghenji]

Thanks Guillaume. You were the one who originally gave me this code. I made few changes as per my need. Here is the complete code and still this am getting result for only '1st' file in the list and not every file.

    [filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
    fullname = cellstr(fullfile(pathname, filename));

    len = length(fullname);      
    wanted_sheet = cell(1, len);

    excel = actxserver('Excel.Application');
    excel.Visible = true;

    for i = 1 : len                    

       workbook = excel.Workbooks.Open(fullname{i});     
       numsheet = workbook.Worksheets.Count;                
       namergb = cell(numsheet, 2); 

       for sheetidx = 1:numsheet
          sheet = workbook.Worksheets.Item(sheetidx);
          namergb{sheetidx, 1} = sheet.Name;
          bgr = sheet.Tab.Color;
          namergb{sheetidx, 2} = uint8([mod(bgr, 256), floor(mod(bgr/256, 
          256)), floor(bgr/65536)]);
        end

    t = cell2table(namergb, 'VariableNames', {'SheetName', 'RGB'});
    [wanted_sheet{i}] = t.SheetName(all(t.RGB == uint8([0 255 1]), 2));
    workbook.Close();

end

    excel.Quit()
    wanted_sheet = [wanted_sheet{:}];
    wanted_sheet = unique(wanted_sheet);

can you help me with changes to be made?

[Guillaume]

This would be slightly cleaner:

[filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
fullname = fullfile(pathname, filename));
wanted_sheets = cell(1, numel(fullfname));
required_rgb = [0 255 1];
required_bgr = polyval(fliplr(required_rgb), 256);

excel = actxserver('Excel.Application');
excel.Visible = true;  %for debugging only

for filenum = 1:numel(fullname)                  
   workbook = excel.Workbooks.Open(fullname{filenum});     
   for sheetidx = 1:workbook.Worksheets.Count; 
      sheet = workbook.Worksheets.Item(sheetidx);
      if required_bgr == sheet.Tab.Color
         wanted_sheets{filenum} = sheet.Name;
         break;
      end
   end
   if isempty(wanted_sheets{filenum})
      warning('File %s does not have a tab of the required colour', filename{filenum});
   end
   workbook.Close();
end
excel.Quit();

But I see no reason why your code would not go through all the files. Can't you see all the files opening in excel?

Of course, if a file does not have a tab with the exact rgb colour [0 255 1] then wanted_sheet will be empty for that file.

edit: bugfix with sheet.Name

[Ghenji]

firstly, few changes needed here Line

wanted_sheets{filenum} = sheet.SheetName;

Shouldn't it be like this -

wanted_sheets{filenum} = sheet.Name;

Now I get what the problem was with only one file being read. There is difference in what color excel passes and what is being read by code. For ex. I set [0 255 2] in excel but answer with matlab code was [0 255 3]. So I changed code to look for [0 255 3]. Now with my code it gave me error

Error using horzcat
Dimensions of matrices being concatenated are not consistent.

This was because out of 3 spreadsheets, 2 spreadsheets have 2 sheet labels of same color.

Now with your code it was not able to detect that color[0 255 3] thats why

Warning: File Copy of BMI260 WMR_A49-A56.xlsm does not have a tab of the required colour 
Warning: File BMI260 (004) with labels.xlsm does not have a tab of the required colour 
Warning: File Copy of BMI260 WMR V07-V09 Samples rev1.xlsm does not have a tab of the required colour.

In short What I need with wanted_sheet variable is result from all the sheets in single matrix. if there is repetition of name then just one name. Never understood why there is difference in reading colors.

[Guillaume]

Yes, there may be some small mistakes in my code, it's obviously untested. I don't even have matlab on this computer. Indeed it should be sheet.Name.

The modified code above stops iterating through the sheets at the first sheet with the required tab colour, so it already does what you want.

As for the off by one error, no idea where it comes from. I'm fairly confident that my conversion from bgr to rgb triplet is correct. In any case, the modified code above converts the desired rgb triplet into the required bgr value instead, using a simpler method. Does it solve the issue?

[Ghenji]

No i don't want it to stop at first iteration it should look for all the sheets of same color and if there is repetition it should give me just one name. here is ex. with file and sheets inside

sheet2 and sheet5 are repeating in file1 and file3. end answer should be -

wanted_sheets = sheet2, sheet3, sheet4, sheet5, sheet7

[Guillaume]

Well

unique([wanted_sheets{:}])

with your original code would have given you what you want

With a cleaner code:

[filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
fullname = fullfile(pathname, filename));
wanted_sheets = cell(1, numel(fullfname));
required_rgb = [0 255 1];
required_bgr = polyval(fliplr(required_rgb), 256);

excel = actxserver('Excel.Application');
excel.Visible = true;  %for debugging only

for filenum = 1:numel(fullname)                  
   workbook = excel.Workbooks.Open(fullname{filenum});
   numsheet = workbook.Worksheets.Count;
   names = cell(numsheet, 1);
   colours = zeros(numsheet, 1);     
   for sheetidx = 1:numsheet
      sheet = workbook.Worksheets.Item(sheetidx);
      names{sheetidx} = sheet.Name;
      colours(sheetidx) = sheet.Tab.Color;
   end
   wanted_sheets{filenum} = names(colours == required_bgr);
   if isempty(wanted_sheets{filenum})
      warning('File %s does not have a tab of the required colour', filename{filenum});
   end
   workbook.Close();
end
excel.Quit();
wanted_sheets = unique([wanted_sheets{:}]);

[Guillaume]

Oh, yes sorry, it needs to be a vertcat instead of horzcat (aka []) since it's a cell array of column vectors

wanted_sheets = unique(vertcat(wanted_sheets{:}));

[Ghenji]

And that's what was missing. Thanks lot Guillaume once again.