Call external executable from excel 2007
Hi everybody, something crazy is happening to me. I wrote an external executable ("graph.exe") which is supposed to save a .xlsm file,
close it, read some data from it, do some calculations, and print the results back into the same spreadsheet. When I double click "graph.exe" everything works. So I created a button in the spreadsheet I mentioned that should call "graph.exe" and therefore initiate the whole process. Here is the code:
When I click the button the .xlsm file is saved and closed, BUT than an error happens and "graph.exe" is not able to read any data from the sheet just closed! I tried to define an hyperlink to "graph.exe" as well, but I get the same problem! Does anyone know why? I was thinking it may have something to do with excel security settings or privacy options, but at this point I don't have a cluePrivate Sub CommandButton2_Click() RetVal = Shell("C:\Users\manuel\Documents\MATLAB\graph.exe", 1) End Sub. Any suggestion would be greatly appreciated.
Last edited by Manuel_; 11-20-2009 at 02:29 PM. Reason: forgot tags for code
It's out of my league, sorry if completely irrelevant, but just a wild guess:
Could you force your graph.exe to wait a little? Just to let Excel complete whatever it might be doing. Perhaps in a loop checking if file is accessible.
I tried already, but it seems that it does not make any difference whatsoever. As soon as the pause comes to an end and the .xlsm is called I get the same error.
What does that error exactly say?
How far exactly does graph.exe get? As i understand, it uses some kind of automation (relevant code bits might be clarifying - for smarter people), succeeds in contacting Excel, forcing save and close and fails when it tries to read data? But it sure finds that file? You do not specify path to workbook to graph.exe - is it hardcoded? Working directory does not change? Little debugging on graph.exe might give useful hints.
And your workbook is not password protected or anything. All files are on a same filesystem and having no special permissions applied. And graph.exe works exactly as needed if called manually, problem arises only when you call it from the same file it must modify? And having open instance of Excel makes no difference in manual try?
Yes, it does sound crazy.
My second guess was that maybe that call to Shell() might keep workbook open, but little testing shows otherwise. Just to make sure: if you call that Shell() not from same workbook, but from Immediate window or other workbook - does it make any difference?
Re: Call external executable from excel 2007
Thanks for the hint! Actually it did not find the .xlsm file.
When I call graph.exe manually it looks for the .xlsm file in the current directory, BUT when I call graph.exe from inside Excel it looks for my .xlsm file in the Excel default file location! To solve this I added a macro in my .xlsm:
which is run as soon as my .xlsm is opened and changes Excel default dir with my current working dir where I stored my .xlsm. I noticed a bug though. In order to make the change effective I need to restart Excel, otherwise the default dir is not updated. Thanks again.Sub Auto_Open() ' ' Auto_Open macro Application.DefaultFilePath = ActiveWorkbook.Path End Sub
Re: Call external executable from excel 2007
Even better:
Sub Auto_Open() ' Auto_Open macro ChDrive Left(ActiveWorkbook.Path, 1) ChDir ActiveWorkbook.Path End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks