I’ve been dabbling with complex spreadsheet work, and I needed to actually write some code to run in Excel. It turns out that Excel, while it is programmable, is fragile and easy to break.
The first problem I ran into was that as my program was running, I could start another Excel application which would interfere with the Excel instance I was using. I was surprised by this, but reasonably happy that the solution was to simply set the Application.IgnoreRemoteRequests in my instance of Excel, and no longer broken by other Excel instances.
However, a few days later, I discovered Excel was completely broken on my machine. It refused to open a spreadsheet from an email attachment, or from the desktop! This was quite scary, and initially I didn’t know the cause at all. Turns out there are lots of posts on the net about this – and it is relatively easy to fix – go to the Tools|Options|General tab in excel, and make sure that the item “Ignore Other Applications” is UNchecked.
Wait a minute – doesn’t “Ignore Other Applications” and “IgnoreRemoteRequests” sound pretty similar? Why yes it does. It turns out that when an Excel extension temporarily sets this setting, Excel persists it when it closes! Whoa! That is very fragile. This means that if a plugin ever sets this setting and forgets to unset it, the user is left in the lurch forever!
For the non-programmers out there, you might think that this is the fault of the Plugin, and that isn’t completely unreasonable. But more accurately, this is the fault of Excel’s API being simply too fragile. Very few APIs in the programming world work this way – where what seems to be a transient setting is actually saved permanently. Further, if anything goes wrong with the plugin, there is no way to make a “failsafe” for this from the Plugin side. Excel saves this setting behind the programmer’s back, and the programmer has no way to know when this setting is saved. If you can’t know when it is saved, you obviously can’t know exactly when to ‘undo’ it either. We can “hack” it, but we can’t fix it.
So, if you run into this problem, where Excel refuses to load files from email or the desktop, you’ll likely find lots of sites which tell you this same fix (Tools|Options|General|”Ignore Other Applications” = unchecked). And it will work. Those sites may further say something like, “this is usually due to a faulty plugin”. All right, sort of. But Excel is a far too easily broken interface. If you are a programmer, you probably would do best to not write addins for Excel. Your code will probably never be robust.
So now since you joined google, everything that Microsoft does looks bad and that google does, looks right. 🙂
Keep it fair Mike.
Sorry to offend. I just write about topics that comes up.
On that day, I was actually doing excel programming. If you check for this particular error in usenet, you’ll see a *lot* of other people have the same trouble. So the reason I posted was to share that experience, and the solution. It’s pretty scary when you are doing a side project and then when you go to do some real work, excel has stopped working 🙂
For fairness, it should be pointed out that I was using excel because there really isn’t anything else comparable out there. Open Office is good for the basic spreadsheets, but it can’t be scripted very well – or at least I don’t know how to do it.
Isn’t it Microsoft’s fault that it looks bad? It’s no surprise Microsoft can’t make its own programs stable enough to not break on a poor plugin. That’s Microsoft. I expect more quality out of Google than Microsoft.
Calling an API fragile seems a little harsh. Maybe it’s the programmer who’s too fragile to pursue the ramifications of changing an application property. I mean, it is an APPLICATION property, no? Sorry, but I think inadequate testing was the real problem here. (“…a few days later…”? “…completely broken…”??)
Now I’m sounding too harsh. Oops. :/
It doesn’t surprise me that an application setting would be saved upon closing. Would I have done it differently at MS? Maybe. Maybe I would’ve included another property called Application.IgnoreRemoteRequestsTempory or something like that, but it’s not that big of a deal to reset a property to what it was (i.e. save the original value before changing it) before quitting the application.
Harsh, yes, but this API is the definition of fragile. In fact, I’d say it is outright dangerous. Imagine the following use case, which is perfectly normal:
1) Create an excel addin to compute pi, or do some long operation which uses this flag.
2) Power fails (user decides to reboot, power goes out, whatever)
3) When you reboot, Excel won’t load any spreadsheet. You just get a blank screen – no error messages, nothing. Just broken. Since you aren’t running your automation anymore, you don’t suspect it is your program which caused it….
Excel does not provide any API which works around this case. You are required to set the flag in order to run your automation (otherwise the automation can fail if the user happens to start Excel while running the automation), and yet by setting the flag, a power-outage will completely and permanently break your Excel application. Yikes!
BTW – if anyone knows how Microsoft intended automation programmers to use this API in a manner which doesn’t have this brittleness, I’d love to hear it and post the solution.
As for me being a fragile programmer – absolutely! We can definitely agree about that! 🙂
Bingo!!
I was having issues with and Excel addin of mine, and your blog triggered me to find a hack to fix it.
I knew what Excel was doing and why I was having issues with the ignoreremoterequests, but while reading this page the lightning bolt appeared.
Create one instance of Excel, set the IgnoreRemoteRequests to True, create a second instance of Excel, set the IgnoreRemoteRequests of that object to false, close the second instance and you are good to go. The initial instance retains the ignore setting default for new instances of excel restored back to the default.
It does slow your addin a small amount by creating the second instance, but at least for my addin, it was not significant enough to affect performance.
I do a significant amount of Excel addin work and I have to agree, Excel is a powerful API and for many areas it is a great solution. But it is also VERY fragile.
Thanks for the tip, and I love Lookout by the way!!
Rod
For work with MS Excel files there is-excel fix,may try to open the necessary *.xls, *,xlt, *.xlsx, xlsm, *.xltm, *.xltx, *.xlam file and see an error message on the screen instead of it: Microsoft Excel unable to read file, This file is not in a recognizable format, etc,recover data after viruses, problems with media (a hard drive failure, a corrupted CD or DVD, etc.), errors in transferring the file via the LAN or the Internet, etc.
Thanks for this post. My Python program is running an Excel process in the background via COM. It can create a separate Excel process for its task, so it doesn’t “take over” another Excel window that is already open, by calling win32com.client.DispatchEx(“Excel.Application”). But the problem I’m having is as described–if a user double-clicks an Excel file in Windows Explorer while my program is running, then my program’s process is interfered with.
So I’m looking for a solution and this post seems to be saying: here’s the problem, Microsoft is a peanut, too bad there’s no solution. Although Rod posted a hack. Is that the best solution available to us?
I tried Rod’s solution, but it didn’t seem to work, at least not for my scenario. It seems that Excel saves/persists the option at exit, not when it is first changed.
The only “solution” seems to be to make sure, make REALLY REALLY sure that your application sets the option back to its original value before it exits. Your application is not permitted to ever crash or be killed.
I have to agree with Mike’s assessment: the Excel API does not allow developers to create a robust application.