Ever performed a web query in Excel? It’s pretty easy. From the Data menu you click ‘From Web’ in the ‘Get External Data’ group. Then you enter a hyperlink and choose which parts of the page you want to import. Once you click OK that’s it. But what do you do if you should run into an error doing a web query like the following: “Unable to open https://somelocationhere … Cannot download the information you requested”. Fear not. This problem, like many others in life, also has an answer.
In the case of this error Excel is asking URLMON to download a copy of the file given by the web URL (https://www.somelocationhere.com). URLMON has navigated to the URL but the web page contains a header called “pragma: no-cache”. This tells WININET, which is used by Internet Explorer, to avoid saving the page to the IE cache. But the file has to be saved to IE for the download to work properly and because it can’t, Excel returns the error you see above. And the web query fails. Why would someone set such a thing on a web page? I’m glad you asked. There is an entrie KB Article dedicated to doing this – http://support.microsoft.com/kb/234067.
So what can you do about it? Nothing. You’re stuck. Okay, okay that’s not true. There are a couple of things you can do.
1. If you have the ability to make changes to the web page, this is by far the easiest method to resolve the problem. Simply remove the “Pragma: No-Cache” header. What does it look like? Look for something like the following:
<%Response.CacheControl = “no-cache” %>
<%Response.AddHeader “Pragma”, “no-cache” %>
<%Response.Expires = -1 %>
2. There is a setting which allows you to bypass the no-cache check for SSL cites on the client. To do this, do the following:
a. Go to START and in the RUN line type REGEDIT.
b. In the registry navigate to
c. Right click Internet Settings and left click New > DWORD Value (32-bit) and name the new value “BypassSSLNoCacheCheck” without the quotes. Double click
this value and give it a value of 1.
That’s it. You may need to restart Excel, but you don’t need to restart the machine. You should now be able to perform the web query without a problem.