Excel handles hyperlinks in an unexpected manner which can often lead to confusion and frustration when trying to access content that requires authentication.

The problem

Depending on how your web application behaves when processing requests, when a user clicks on a link in Excel, you might get an error similar to this:

Unable to open <url>. Cannot download the information requested. or
Unable to open <url>. The internet site reports that the item you requested could not be found. (HTTP 1.0/404)

This is because rather than Excel just passing the URL over to the user's default browser to open it is instead attempting to verify that the URL exists first, which it does by sending a HEAD request followed by a GET request. If the HEAD returns an error other than a 405 (Not Supported) or if the GET request fails, then Excel will display an error to the user. If they succeed then Excel will finally allow the default browser to open the URL and all is well.

The chance of these requests succeeding by themselves is slim because Excel is making these requests with its own HTTP client which won't have any authentication cookies stored in it, so unless the linked pages don't require any authentication they will fail.

Excel doesn't seem to do any verification of the responses it receives so you can return a totally empty response body or anything else at all.

Solutions

There are several ways of solving this problem.

1. Stop Excel from behaving this way

Your users can set a registry key to prevent Excel from behaving this way.

Obviously this is not a very customer-friendly thing to do!

2. Identify these requests coming from Excel and respond appropriately

Either your web server or your web application can be configured to identity requests coming from Excel and to return a success response rather than trying to process them normally.

The HEAD request uses a UserAgent string that looks similar to this:

Microsoft Office Excel 2014 (16.0.11231) Windows NT 10.0

and the GET request UserAgent looks like this:

Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E; ms-office

By identifying requests with similar UserAgents to these, a 200 result can be returned.

With IIS for example, you can use the following web.config setting:

<rule name="WordBypass" enabled="true" stopProcessing="true">
    <match url=".*" />
    <conditions>
        <add input="{HTTP_USER_AGENT}" pattern="Word|Excel|PowerPoint|ms-office" />
    </conditions>
    <action type="CustomResponse" statusCode="200" statusReason="Refresh" statusDescription="Refresh" />
</rule>

(Source: StackOverflow)

Or you could add code directly into your Web API actions:

[Route("blah/blah")]
public IHttpActionResult Get()
{
  if (Request.Headers.UserAgent.ToString().Contains("ms-office")) return Ok();

  ...
}

Or you could write an ActionFilter:

using System.Net;
using System.Net.Http;
using System.Web.Http.Controllers;
using System.Web.Http.Filters;

public class MicrosoftOfficeFilter : ActionFilterAttribute
{
  public override void OnActionExecuting(HttpActionContext actionContext)
  {
    if (actionContext.Request.Headers.UserAgent.ToString().Contains("ms-office"))
    {
      actionContext.Response = new HttpResponseMessage(HttpStatusCode.OK);
    }
    base.OnActionExecuting(actionContext);
  }
}