Have you ever encountered this problem? When trying to pull data via a web query in Excel you get an error saying “Unable to open https://… <your site location> Cannot download the information you requested.” and clicking OK dismisses the web query. As in no data. What’s going on here?
What’s going on is when you execute a web query, Excel will ask URLMON to download a copy of the file given byu the web URL and URLMON has navigated to the address. However, the page has a “Pragma: no-cache” header (see http://support.microsoft.com/kb/165150 and http://support.microsoft.com/kb/222064 for more information) in it, which tells WININET (which IE uses) to avoid saving the web page to the Internet Explorer cache. In order for the download operation to work, the file has to be saved to IE. Not allowing the file to be cached causes the error you see in Excel and in turn, nobody is happy.
To resolve this situation, there are a couple of things you can do:
1. Edit the web page from where you are pulling your data and remove the no-cache header. You would be looking for a meta tag like the following:
2. You can direct the client computer to set the Internet Explorer option to bypass the no-cache check for SSL sites (remember the https:// in the sample URL above). To do this, go to START and in the RUN line type REGEDIT. In the registry navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings. 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.
Then try the web query again.