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

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.

Answer 1

[Alfonso Nieto-Castanon]

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 :)

[Ned Gulley]

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.

[Alfonso Nieto-Castanon]

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!

Answer 2

[dpb]

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.