How to import Excel data from a spreadsheet with cross-file references?
2 次查看(过去 30 天)
显示 更早的评论
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 个评论
采纳的回答
Alfonso Nieto-Castanon
2015-6-18
编辑:Alfonso Nieto-Castanon
2015-6-18
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 个评论
Alfonso Nieto-Castanon
2015-6-18
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!
更多回答(1 个)
dpb
2015-6-17
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 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!