We use web queries to retrieve data in Excel for reporting purpose. These Web queries are working correctly in Excel 2007, 2010, 2013 and 2016.
But when used in Mac Excel 2016 it fails.
We perform these web queries using VBA and retrieve data generated by a Spring REST interface.
I already tried using http GET instead of http POST and tried performing the web query via the UI ('Data' -> 'Get External Data' -> 'Run Saved Query' using a .iqy file).
Also error seems not to be content related (tried very simple html; <html><body><table><tr><td>col1</td></tr><tr><td>value1</td></tr></table></body></html>).
When using Wireshark to analyze the traffic, the response of the REST call is correct (http 200 with correct content).
Errors reported using UI import using '.iqy' file: "cannot locate the internet server or proxy server".
Error performing web query using VBA: "Error 1004 Application-defined or Object-defined error".
Does anyone have an idea what can cause this behavior?
Found the reason: Spring MVC (REST) has some default headers it sets. One of them is X-Content-Type-Options: nosniff. The way we generated some of the REST response data, resulted in not setting the Content-Type header.
Mac Excel was not able to determine the content type (because of the X-Content-Type-Options: nosniff option).
This can be solved by either not setting the X-Content-Type-Options header or explicitly set the Content-Type header.
Bonus info: I've used WireMock to simulate REST interface and was able to strip headers one by one exposing the culprit.