How to import Excel data from a spreadsheet with cross-file references?
2 views (last 30 days)
Ned Gulley on 17 Jun 2015
I have an Excel spreadsheet, call it Spreadsheet A, that I want to read into MATLAB. The Import Tool works well for this.
Except for one thing.
Spreadsheet A uses Excel's VLOOKUP function to include fresh data from Spreadsheet B every day. In order for MATLAB to "see" the latest data, I have to open Excel and re-save Spreadsheet A, even though the file as written doesn't change (although obviously something is changing in there).
Can anyone suggest ways to automate this process, ideally from the MATLAB side? I'd rather not open and re-save the file manually every morning.
Alfonso Nieto-Castanon on 18 Jun 2015
Edited: Alfonso Nieto-Castanon on 18 Jun 2015
I clearly would not recommend this as a first choice, but if nothing else works then perhaps you can simply have Matlab open, save, and close your excel file using keyboard shortcuts. Something like:
% open file (and give it a little time to get that done)
% press CONTROL+S (save)
% press CONTROL+W (close)
This example is for PC's. Things would be just slightly different on Macs (e.g. use VK_META for cmd key, use system('open... instead of winopen) or Unix but the same idea still applies.
ps. See this Cody problem for a few more examples of java.awt.Robot uses :)
More Answers (1)
dpb on 17 Jun 2015
Use the COM or .NET Framework would be only thing I'd see. Somehow Excel will have to be told what to do (altho I guess you could have a chron process that was scheduled to do it at some time before you begin the Matlab session.