I found many similar questions about this problem on stackoverflow and I think I composed a portable and "definitive" solution to this problem.
First of all: why is the browser complaining about the user session?
The answer is "Microsoft Office Protocol Discovery". In a few words: it's something that works only if you are using Microsoft Windows and your default browser is Internet Explorer.
Basically, if you are not using Microsoft Windows OR your default browser is not Internet Explorer, when you click on an URL, the request sent to the browser will always be with an empty cookie. This means that, despite the default browser could use a correct cookie to authenticate the user, the request coming from Excel will never use it. But if you try to reload the page (and the webserver is not redirecting to a different error page), the browser will use the domain cookie and you'll see the correct page.
Second question: how can I solve this problem?
I think I found a very good solution, composed by an HTML part and a webserver part.
HTML part
Starting from the fact that you need to reload the page to use the cookie, I created a simple static page containing a little javascript code and some html. This is just an example. The main part of this code is here.
<!DOCTYPE HTML>
<html lang="en-US">
<head>
<script type='text/javascript'>
function getParameterByName(name) {
var match = RegExp('[?&]' + name + '=([^&]*)').exec(window.location.search);
return match && decodeURIComponent(match[1].replace(/\+/g, ' '));
}
</script>
<meta charset="UTF-8">
<script type="text/javascript">
window.location.href = getParameterByName('newUrl');
</script>
<title>Page Redirection</title>
</head>
<body>
<!-- Note: don't tell people to `click` the link, just tell them that it is a link. -->
If you are not redirected automatically, follow the <a href='<?php echo $newUrl; ?>'>link</a>
</body>
</html>
You can access to the querystring via javascript in many ways, you can find a very interesting thread here.
This static page, let's call it redirect.html, will only do one thing: it will redirect the browser to the page specified in the newUrl parameter. Now if I put in the A1 cell something like:
http://www.mypersonalwebsite.com/redirect.html?newUrl=http://www.mypersonalwebsite.com/url/visible/only/to/loggedin/users
and if I click on this URL:
- Excel will go to this URL using the default browser
- The browser will open the redirect.html page with an empty cookie
- The browser will reload the page using the domain cookie
- The user will see the correct page as an authenticated user
The pros of this trick are: it works on all platforms and on all browsers supporting javascript. The cons are that we need to modify all URLs in all our Excel folders.
The webserver part
To hide this redirection to the end users, and save us to modify all our Office documents, we can use another trick. In this example I will use nginx:
if ($http_user_agent ~* "(Excel|PowerPoint|Microsoft Office)") {
rewrite ^/(.*)$ /redirect.html?url=$1 break;
}
The meaning of this little if block is: if the incoming request is from a user agent like Excel, Powerpoint and so on, nginx will do an internal redirection to the redirect.html page, that will again do the browser redirection explained above.
This nginx redirect will completely hide the redirect trick, so we can use the original URLs and the users will always see the correct page.
I'm sure all this can be improved, and I would like to learn how to do it.
I hope this will help someone in finding a complete solution to this Office problem.
