How to import Excel data from a spreadsheet with cross-file references?

2015 6 17
2 个回答
回答已采纳
更新时间：2015 6 18
8 次查看（30 天）

0 个投票

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
编辑：Alfonso Nieto-Castanon 2015-6-18

1 个投票

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.
ps. See this Cody problem for a few more examples of java.awt.Robot uses :)

2 个评论
显示 无 隐藏 无

Ned Gulley
Ned Gulley 2015-6-18
Truly Alfonso, you are the man. It was clear that any solution would have to involve opening Excel, and you've given me a nice way to do that remotely from MATLAB (not to mention a Cody tie-in!). Thanks.
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
dpb 2015-6-17

0 个投票

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.

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by