Partager via

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

  • Article

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 - https://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

  • Anonymous
    January 01, 2003
    tried the second solution--it did not work--returned to confirm setting in registry entry ok--now what?

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

  • Anonymous
    March 14, 2012
    even i get this error for http. and above setting did not resolve my issue.

  • Anonymous
    April 08, 2012
    I did everything step by step and it did not work for me either.  

  • Anonymous
    June 26, 2012
    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...

  • Anonymous
    August 12, 2012
    Hey thanks so very much...the 2nd option really worked for me...Thanks again

  • Anonymous
    October 27, 2012
    Yes! the second option fixed my problem as well

  • Anonymous
    November 27, 2012
    2nd option helped. Thanks immensely

  • Anonymous
    February 21, 2013
    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?

  • Anonymous
    August 13, 2013
    Thank you so much, its worked for me :)

  • Anonymous
    December 07, 2014
    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

    • Anonymous
      April 04, 2017
      @Paul, I have been using web data from same site for years. Now 'Unable to open' error. Not sure why, other than recent Mcafee update. Tested from another machine with same update and works fine.

  • Anonymous
    June 09, 2016
    Option 2 resolved my issue completely. I was not able to refresh via BigQuery connector (on Excel 2010), even though it had worked consistently before. This was the fix I needed THANK YOU!! <3