What do I need to do to make Excel access a Web Query via HTTPS?

excel https excel-web-query
Joachim Sauer picture Joachim Sauer · Feb 5, 2010 · Viewed 15.5k times · Source

I'm trying to implement a page that allows Excel users to use the data it provides via the Web Query feature provided by Excel.

It's all working out pretty nicely, as long as I use HTTP (even BASIC user authentication works).

As soon as I switch that over to HTTPS Excel won't download the data anymore (it's even a fully official SSL certificate, so it's not a problem with a self-signed one).

This Microsoft knowledge base article pretty much describes the problem.

Now the part that makes me wonder is this:

This issue occurs when Excel cannot initiate a connection because of the settings on the secure Web server.

This seems to imply that there is some way to get this working, but there's not even a hint at the direction I need to look at.

Should the "because of the settings on the secure Web server" be taken at face value, or is it just a Microsoft way of saying "this won't work unless you buy the right software from us"?

Answer

Joachim Sauer picture Joachim Sauer · Feb 5, 2010

It seems I've found the problem:

MS Excel seems to be unable to use the data on the page if the HTTP headers of the page specify that it should not be cache and it is transfered via HTTPS (the same headers sent via HTTP seem to get ignored).

So by not sending these headers, Excel was suddenly able to access the data:

Pragma: no-cache
Cache-Control: no-cache

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More