How to import Excel data from a spreadsheet with cross-file references?
3 views (last 30 days)
Show older comments
Ned Gulley
on 17 Jun 2015
Edited: Alfonso Nieto-Castanon
on 18 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.
0 Comments
Accepted Answer
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:
Keyboard=java.awt.Robot;
% open file (and give it a little time to get that done)
winopen(myexcelfile);
pause(5);
% press CONTROL+S (save)
Keyboard.keyPress(java.awt.event.KeyEvent.VK_CONTROL);
Keyboard.keyPress(java.awt.event.KeyEvent.VK_S);
Keyboard.keyRelease(java.awt.event.KeyEvent.VK_S);
Keyboard.keyRelease(java.awt.event.KeyEvent.VK_CONTROL);
% press CONTROL+W (close)
Keyboard.keyPress(java.awt.event.KeyEvent.VK_CONTROL);
Keyboard.keyPress(java.awt.event.KeyEvent.VK_W);
Keyboard.keyRelease(java.awt.event.KeyEvent.VK_W);
Keyboard.keyRelease(java.awt.event.KeyEvent.VK_CONTROL);
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.
2 Comments
Alfonso Nieto-Castanon
on 18 Jun 2015
Thanks Ned! Using java.awt.Robot is a kind of fun way to automate tedious tasks using Matlab (and it is a simple/fast way to get Matlab to interact with arbitrary external programs). When working with Excel perhaps the COM approach dpb mentioned will give you greater flexibility (e.g. see this post) but I have never really tried that so I do not know the specifics. Good luck!
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.
0 Comments
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!