I've always had mixed feelings for exceptions. First of all exceptions should only be used for exceptional things, not things that are expected. For example if you open a file it may fail for several reasons; it does not exists, you do not have rights to open it or it may be locked. These are all perfectly reasonable errors and throwing an exception for these failures is in my opinion not a great API design. If there are other methods to check for all these circumstances it might be OK but not optimal. A reasonable compromise is to have a TryOpen method which never throws an exception. This gives the user of the API the option of letting file open errors being expected or fatal.
Because exceptions should only be thrown for exceptional circumstances there should be no reason to catch them other in the application's main loop unless you can add information to the error in which case you want to wrap and throw a better exception. The exception to this rule is when you use an API (such as file open mentioned before) that throws for pretty common error cases you want to ignore.
Another thing I don't like with exceptions is the fact that you (in C++ and C#, Java is better here) get no help from the compiler to know what types of exceptions a function may call. So in C++ and C# you may think you catch everything you need but you really don't which leads to unwanted behavior in your application. It is just too easy to get into a situation where you think a number of lines are going to be executed but they're not for some exception.
However if you use return codes (as you would in a C program) you get your code cluttered with "if (previous called failed) handle error" which hides what the code really does. This can be hidden using macros to handle errors (ex: result = DoSomething ON_FAIL_RETURN(result); ). And if there is no error handling it is hard to know what happens when the next line is executed even though there was an error.
Using exceptions is also a kind of "I failed and I hope somebody else somewhere can handle it" while the use of return codes defines a clear contract "I failed and you who called me must handle it". So even though exceptions are convenient, return codes feel more defensive, i.e. are easier to get right resulting in less bugs but sometimes at the cost of less clear code. As usual the mix of both approaches is probably the best. But there are more alternatives. If you use adverse as described by Feathers I'm prepared to accept exceptions to a much larger degree because they're being handled immediately so it reminds of the return code approach.