Error performing web query - "Cannot download the information you requested"


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" %>

or

<%Response.AddHeader "Pragma", "no-cache" %>

or

<%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

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings

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.

Comments (11)

  1. Anonymous says:

    tried the second solution--it did not work--returned to confirm setting in registry entry ok--now what?

  2. nelsonge says:

    again, after signing in: tried the second solution--it did not work--returned to confirm setting in registry entry ok--now what?

  3. thavva says:

    even i get this error for http. and above setting did not resolve my issue.

  4. CC Santa Monica says:

    I did everything step by step and it did not work for me either.  

  5. Mario says:

    did the regedit solution and excell 2010 still prompts logon and password to access the website. On 2007 and 2003 noo problem at all. Only in 2010... please someone help here...

  6. Sagar says:

    Hey thanks so very much...the 2nd option really worked for me...Thanks again

  7. Arie says:

    Yes! the second option fixed my problem as well

  8. Inga says:

    2nd option helped. Thanks immensely

  9. Loki says:

    Hi, I have the same problem with Excel 2007 but when I tried the 2nd option the error changed to: the internet site reports that a connection was established but the data is not available. I check this site thru Ms Excel (New Web Query window) and it work fine. Any sugestions?

  10. Rama says:

    Thank you so much, its worked for me 🙂

  11. paul says:

    Sorry this did not work I have been importing stock data into excel from Yahoo finance for months but all of a sudden it si not working

Skip to main content