MS Excel function TODAY() not updating when opened using MATLAB XLSREAD

I use the TODAY() function in an excel worksheet which is accessed using MATLAB (i.e. I don't read the worksheet directly). Now I have to open the excel file just to update the TODAY() function, and this method is a bottleneck in my workflow.

I know it's possible to set Excel to automatically update cells upon being opened under the calculation tab, but apparently this does not apply to MATLAB (or COM interfaces in general?). Can someone give me an idea of what can be done?

EDIT: I'm using MATLAB's built in 'xlsread' command

Answer 1

[Shruti Sapre]

Hi Chris,

I understand that you want to get an updated date into MATLAB without having to manually open the Excel Sheet.

I tried this with the “Now” function in Excel, and I could see the updated values using “xlsread” so I’m not sure if I have misunderstood your question.

Alternatively, you could try the below method to read data from Excel:

Since opening the sheet manually updates it, but “xlsread” in MATLAB doesn’t, I tried to use the “actxserver” in MATLAB to achieve a similar effect. Here is a sample code:

%Create server for Excel

excelObj = actxserver ('Excel.Application');

% Full path to your file required

fileObj = excelObj.Workbooks.Open('test.xlsx');

% Get the required sheet from the Workbook

sheetObj = excelObj.Worksheets.get('Item', 'Sheet1');

%Read the cell value into a variable called resDate

Range = get(sheetObj, 'Range', 'A1:A1');

resDate = Range.value;

fileObj.Save;

excelObj.Quit;

delete(excelObj);

This creates a server for Microsoft Excel in MATLAB, opens the file and gets the required sheet in the workbook, reads the data, saves the file and then closes it.

This might enable you to get the updated data from the sheet without having to manually open it.

Hope this helps!

-Shruti

[Chris Coutinho]

Hello Shruti,

Thanks for your very detailed response. I think I have discovered what my issue was originally: I attempted to read an xls file in a Linux environment instead of a Windows environment where MS Office is installed. I assumed the calculations could be done in the worksheet itself, but in fact MS Excel does the calculations. Without Excel, the worksheet does is not updated.

This also does not allow me to use your actxserver option because I do not have Excel on my Linux machine.

Thanks for your response!

Cheers, Chris