Update an Excel file after each Matlab calculation

Dear everyone,

I need to communicate between Matlab file and a very complicated excel file with several sheets which are connected to each other. All Excel sheets will be calculated after the matlab result was filled in one certain cell in Excel. Does anybody know a code line in Matlab how to refresh the whole excel file automatically from Matlab (last code line)? Excel is not able to this by its own.

Thank you very much!

add_in_path = 'xxx';     % Ordnerpfad
add_in_filename = 'REFPROP.xla';                                    % Dateiname
%---------- Excel connection
file_path = 'xxx';    % Ordnerpfad
filename = 'xxx';                % Dateiname
spreadsheet_name = 'xxx';                       % Name des Tabellenblatts
% Import area
spreadsheet_range = 'xxx';       % Zellenbereich
%% Start
%--- COM-Server start
[Excel, File] = Excel_interaction_Start_COM_Server(add_in_path, add_in_filename, file_path, filename);

    %% Matlab calculation ongoing
%Write the result in the Excel file    
xlswrite1(File,rawData_mod,spreadsheet_name,spreadsheet_range_export);

    %% Excel refresh ?????????

[Dyuman Joshi]

Firstly, Do not use the deprecated xlswrite. Use readmatrix instead.

Secondly, Is it necessary to update the file after each calculation or the calculations can be performed all at once and then the file can be updated?

[Timo]

Thanks Sir,

unfortunately it is necessary to update it after each calculation.

What is the advantage to use the readmatrix command instead of xlswrite?

[Stephen23]

WRITEMATRIX, WRITECELL, WRITETABLE have more options, better cross-platform support, and are generally more efficient.

XLSWRITE, DLMWRITE, CSVWRITE, etc are not recommended, as the documentation clearly states:

Answer 1

[Stephen23]

You can use a COM server to trigger Excel to recalculate the worksheets:

https://www.mathworks.com/matlabcentral/answers/100938-how-can-i-run-an-excel-macro-from-matlab?

A basic outline:

• make sure you use a macro-enabled Excel workbook e.g. XLSM.
• write a VBA macro that calculates the worksheets/ranges that you require using e.g. https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.calculate(method)
• open a COM server from MATLAB:
• write the value/s, then
• call the VBA macro.

[Timo]

Hi Stephan23,

maybe it is more clear if I provide some more code below. I am also using an external REFPROP Excel AddIn to calculate physical substance data. The Function we are using is: Excel_interaction_Start_COM_Server. The questions are afterwards.

Thank you so much!

disp('Starting COM server to access Excel file...');
tic
Excel = actxserver('Excel.Application');
%Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Open(fullfile(add_in_path,add_in_filename));
%Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
Excel.Workbooks.Item(add_in_filename).RunAutoMacros(1);
%File='C:\YourFileFolder\FileName';
File=fullfile(file_path,filename);
if ~exist(File,'file')
    ExcelWorkbook = Excel.Workbooks.Add;
    ExcelWorkbook.SaveAs(File,1);
    ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);
toc
disp('Done.');

Afterwards you will find the code where we need the Excel Refresh of the full workbook. To calculate the whole workbooks Excel takes aroung 15 seconds. Do you know a function or code line to do this in matlab?

clear
close all
clc

diary Diary_Matlab_Excel

%% User Input

% Angaben zum relevanten Excel Add-in
add_in_path = 'C:\XXX';     % Ordnerpfad
add_in_filename = 'REFPROP.xla';                                    % Dateiname


%---------- Reale Daten
file_path = 'XXX';    % Ordnerpfad
filename = 'XXXm';                % Dateiname
        filename_complete = 'XXX';
spreadsheet_name = 'Kompressor';                       % Name des Tabellenblatts


% Import-Bereich spezifizieren
spreadsheet_range = 'A1:N138';       % Zellenbereich, in dem die relevanten Parameter stehen für die Parameterstudie, sprich die zu variierenden Werte und die Ergebnisse (in typischer Excel-Notation, z.B. 'A1:B2')

pause_time = 15;

%% Start

%--- COM-Server starten, der die Interaktion zw. Matlab und Excel ermöglicht
   [Excel, File] = Excel_interaction_Start_COM_Server(add_in_path, add_in_filename, file_path, filename);


%% We need at this place an full Excel Workbook Refresh and a save of the excel file (also below, its like a chain)

%----- Einlesen der Daten aus Excel
disp(' ');
disp('Reading excel...');
tic
[~, ~, rawData] = xlsread1(File,spreadsheet_name,spreadsheet_range);
toc
disp('Done.');


    %External Matlab Skript to calculate 

    %The results of the external Matlab script calculation needs to be
    %put back into the excel file

rawData_mod = rawData(112,4);
rawData_mod{1,1} = output.shell_T_out;
spreadsheet_range_export = 'D112';

disp('Writing excel...');
xlswrite1(filename_complete,rawData_mod,spreadsheet_name,spreadsheet_range_export);
disp('Done.');

disp('Saving Excel ActiveWorkbook...');
excelObject.Workbooks.Item('XXX').Save;
disp('Done.');

disp_output = sprintf('Pause (%.1i s)...',pause_time);
disp(disp_output);
pause(pause_time);
disp('Done.');

%% We need at this place an full Excel Workbook Refresh and a save of the excel file (also below, its like a chain)
                       

%% Similar Calculation again
clear rawData;
clear rawData_mod;
clear output;
clear input;

%----- Einlesen der Daten aus Excel
disp(' ');
disp('Reading excel...');
tic
[~, ~, rawData] = xlsread1(File,spreadsheet_name,spreadsheet_range);
toc
disp('Done.');


    %External Matlab Skript to calculate 

    %The results of the external Matlab script calculation needs to be
    %put back into the excel file

rawData_mod = rawData(112,5);
rawData_mod{1,1} = output.shell_T_out;
spreadsheet_range_export = 'E112';

disp('Writing excel...');
xlswrite1(filename_complete,rawData_mod,spreadsheet_name,spreadsheet_range_export);
disp('Done.');

disp('Saving Excel ActiveWorkbook...');
excelObject.Workbooks.Item('XXX').Save;
disp('Done.');

disp_output = sprintf('Pause (%.1i s)...',pause_time);
disp(disp_output);
pause(pause_time);
disp('Done.');

%% Some more loops to come afterwards within the same scheme

[Stephen23]

"Do you know a function or code line to do this in matlab?"

Yes: the ACTXSERVER method RUN, that you use to run a macro that calls the worksheet method CALCULATE. Exactly as per my answer.