How to assign a function to an Excel cell

I have been using xlswrite to write Excel functions into cells in an Excel spreadsheet. When opening the spreadsheet the functions evaluate as expected. Now Mathworks is calling xlswrite "not recommended". I've been unable to find a way to accomplish the same thing without xlswrite. How can I use another builtin Matlab function to perform the same as the xlswrite?

% Ways to write to Excel

FN = 'TestFile.xlsx';

% Put some values into the test file

Vals = num2cell(rand(10,3));

writecell(Vals, FN, 'range', 'a1');

% Function for an Excel cell

XFunction = {'=SUM(B1:B10)'};

% This creates a function in Excel that works

xlswrite(FN, XFunction, 1, 'D1');

% This does not

writecell(XFunction, FN, 'Range', 'D2')

winopen(FN)

[Stephen23]

Perhaps:

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

Answer 1

[Shivani]

Hello @Ronald Stahl

Based on my understading, there is no direct way to write Excel formulas into an Excel file using "writematrix", "writecell", or "writetable" in a way that Excel recognizes them as formulas. However, there is an alternate approach to writing into an Excel file without using the "xlswrite" function. This can be achieved by using ActiveX on Windows.

You can use ActiveX to interact directly with Excel, enabling you to write formulas into cells. This method works around the limitation of "writecell" and similar functions by programmatically controlling Excel to input the formula, ensuring that Excel correctly interprets it as a formula rather than a string.

Kindly refer to the following sample code on how ActiveX can be used to write the formula into an Excel cell:

FN = 'TestFile.xlsx';

Vals = num2cell(rand(10,3));
writecell(Vals, FN, 'Range', 'A1');

Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open(fullfile(pwd, FN));
Sheet = Workbook.Sheets.Item(1);

XFunction = '=SUM(B1:B10)';
Range = Sheet.Range('D1');
Range.Formula = XFunction;

Workbook.Save;
Workbook.Close;
Excel.Quit;
delete(Excel);

winopen(FN);

Please note that this approach is Windows-specific due to the use of ActiveX controls, which are not available on macOS or Linux.

I hope this answers your question!