[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);
