Opening and closing multiple excel files

I am trying to run a code (R2016b) that uses Activex to open multiple excel workbooks at once, read/write to them, and then close them all when finished. Below is a paired-down version for the purposes of answering this question (supposing only 2 excel files - in reality there are 38). The problem I am running into is that after I run the code, if I go to open the workbooks in Excel they behave as if someone else has them open for editing (i.e., still open by Activex/Matlab). Any thoughts on how I can improve my code to prevent this annoying problem?

Excel = actxserver('Excel.Application');
File1 = 'Path1';
File2 = 'Path2';
Excel.Workbooks.Open(File1);
Excel.Workbooks.Open(File2);
%Insert code for read/write/etc code here
Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete
clear Excel

[Stephen23]

https://www.mathworks.com/help/matlab/matlab_external/using-a-matlab-application-as-an-automation-client.html

"Make sure that you close workbook objects you create to prevent potential memory leaks."

You need to close the workbooks.

[Zach Karthauser]

Thank you for responding. I figured that much, but I don't know the exact syntax to do so. The syntax in the link you provided doesn't seem to work (keep in mind that I'm running R2016b, and the documentation is for R2022a)

Answer 1

[Varun]

Hi Zach,

The issue you're experiencing in MATLAB version R2016b with Excel workbooks behaving as if they are still open for editing after running your code might be due to the way you are closing the workbooks and quitting the Excel application. To ensure proper closure and release of resources, you can modify your code as follows:

% Create Excel server object 
Excel = actxserver('Excel.Application'); 
try 
    % Disable Excel alerts and visibility 
    Excel.DisplayAlerts = false; 
    Excel.Visible = false; 
    
    % Open the workbooks 
    File1 = 'Path1'; 
    File2 = 'Path2'; 
    Workbook1 = Excel.Workbooks.Open(File1); 
    Workbook2 = Excel.Workbooks.Open(File2); 
    
    % Insert code for read/write/etc. operations here 
    
    % Save and close the workbooks 
    Workbook1.Save; 
    Workbook1.Close; 
    Workbook2.Save; 
    Workbook2.Close; 
    
    % Quit Excel and release resources 
    Excel.Quit; 
    Excel.delete; 
    
catch exception 
    % In case of an error, ensure proper cleanup 
    if exist('Workbook1', 'var') 
        Workbook1.Close; 
    end 
    if exist('Workbook2', 'var') 
        Workbook2.Close; 
    end 
    Excel.Quit; 
    Excel.delete; 
    throw(exception); 
end 

% Clear variables 
clear Excel Workbook1 Workbook2 

In this modified code, I've added error handling to ensure that workbooks are closed, and Excel is properly quit even if an error occurs during the execution of your code. This helps prevent any potential issues with workbooks being left open by the Excel server.

Additionally, I've disabled Excel alerts and set the visibility to false to prevent any pop-up messages or Excel windows from being displayed during the execution.

Hope this helps.