error: The object invoked has disconnected from its clients.

As I am running a loop that opens and checks excel files, the loop is forced to stopped with the error:

error: The object invoked has disconnected from its clients.

Can anyone tell me why this error is occurring?

This is my code:

filelist = {data.AllExcelPath};
hassheet = false(size(filelist)); 
excel = actxserver('Excel.Application');    
cleanupobj = onCleanup(@() excel.Quit);   
for fileidx = 1:numel(filelist)
 try
    workbook = excel.Workbooks.Open(filelist{fileidx}, false, true);
 end
    sheetnames = arrayfun(@(i) workbook.Sheets.Item(i).Name, 1:workbook.Sheets.Count, 'UniformOutput', false);
    workbook.Close(false);  
    if ismember('Cooking_is_fun', sheetnames)
       hassheet(fileidx) = true;
    end
    wantedExcel = filelist(hassheet);
end

It seems that no one has had this error before, and it is very hard to figure out whether it is a problem with my code or VBA or else... Does anyone have a clue why this is happening? Any help/discussion will be greatly appreciated! Thanks!!

Answer 1

[Guillaume]

[Pedantic] The error certainly has nothing to do with VBA since there's no VBA involved. You're using COM automation to talk to excel, which is a completely different thing. If you were writing the code in VBA you'd be using the same COM automation to talk to excel, but the two are completely distinct [/end pedantic].

I strongly suspect the problem is caused by the improper scoping of your try statement. Here is what happens:

• loop iterates, enter try statement.
• in the try statement, you open an excel file and it succeeds. workbook is now a valid object.
• query the workbook for the list of work sheets
• workbook is closed.
• next iteration of the loop, enter try statement
• in the try statement, try to open the excel file, it fails. skip to the end of the try. no assignment is made to workbook. workbook is thus the work book from the previous iteration, which is now closed.
• query the closed workbook for the list of sheets, cue: The object invoked has disconnected from its clients

To fix this, you need to include all the code that refers to the workbook in the try statement:

filelist = {data.AllExcelPath};
hassheet = false(size(filelist)); 
excel = actxserver('Excel.Application');    
cleanupobj = onCleanup(@() excel.Quit);   
for fileidx = 1:numel(filelist)
   try %hassheet is false by default, so if try fails nothing needs to change
      workbook = excel.Workbooks.Open(filelist{fileidx}, false, true);
      sheetnames = arrayfun(@(i) workbook.Sheets.Item(i).Name, 1:workbook.Sheets.Count, 'UniformOutput', false);
      workbook.Close(false);  
      if ismember('Cooking_is_fun', sheetnames)
          hassheet(fileidx) = true;
      end
   end 
wantedExcel = filelist(hassheet);

edit: another option is to skip the rest of the loop in case of error:

for fileidx = 1:numel(filelist)
   try 
      workbook = excel.Workbooks.Open(filelist{fileidx}, false, true);
   catch
      continue; %skip to next iteration if an error occur
      %hassheet is already false anyway
   end
   sheetnames = arrayfun(@(i) workbook.Sheets.Item(i).Name, 1:workbook.Sheets.Count, 'UniformOutput', false);
   workbook.Close(false);  
   if ismember('Cooking_is_fun', sheetnames)
       hassheet(fileidx) = true;
   end
end 
wantedExcel = filelist(hassheet);

[chlor thanks]

You are my hero!! I almost give up on hope and you saved my life again! Thank you thank you thank you I cannot say thank you enough, please take my ultimate gratitude...THANK YOU SIR, Guillaume!