MS Excel function TODAY() not updating when opened using MATLAB XLSREAD
8 views (last 30 days)
Show older comments
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
0 Comments
Answers (1)
Shruti Sapre
on 1 Sep 2015
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
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!