MATLAB File IO to Excel Spreadsheet Running Excel Add-in

I have an Excel sheet that uses an Excel Add-in. This add-in essentially calculates useful output values from certain inputs. These values are displayed in multiple cells.

I would like to write various inputs from MATLAB into this sheet and then read in the results. I tried using functions like 'xlswrite', 'xlsread' and the newer 'writematrix' and 'readmatrix'. Writing to cells works fine. However, when reading the cells that contain output from my Excel Add-in, MATLAB reads in gibberish instead of my expected values. Sometimes, MATLAB also gives me error codes related to these failed operations.

How can I read in the data I want from Excel?

If it helps, I'm using Excel 2010 and MATLAB R2019b on a Windows machine.

Answer 1

[MathWorks Support Team]

Using COM to connect to Excel is a great way to accomplish this. Note that this approach will not work for non-Windows machines, since COM is used.

Attached is a simple script ("excel_IO_demo.m") and a simple spreadsheet ("testSheet.xlsx") that shows how one can connect to the Excel spreadsheet. Note that this spreadsheet does not use an Excel Add-in for simplicity, but the workflow is the same. You can verify that this works for Excel Add-ins if desired by testing this yourself. Note that the Excel file must be open already before running the script.

Note that connecting to Excel with 'actxGetRunningServer' works in Excel 2016 and 2013, but this did not work as expected in Excel 2010. Specifically, "e.Workbooks.Count" would return 0 if the Excel file was opened a certain way, which makes interaction impossible.

Note that Excel would not load the add-in if started with "actxserver('Excel.Application')". This is the reason why "actxGetRunningServer" is used.