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.
![]() -->
EXcel - unable to open URL: Cannot download the information you requested Hot Network Questions What are the SEO implications of an established website shifting. Reference: https://support.microsoft.com/en-us/help/218153/error-message-when-clicking-hyperlink-in-office-cannot-locate-the-inte.
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_USERSoftwareMicrosoftWindowsCurrentVersionInternet 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.
Hi, I have a list of names with all their contact details on one sheet, and a second sheet with a list of To Do's. The name of my Workbook is To Do's.xls On Sheet 1, next to the first column of names, I have created a second column with a copy of all the names, highlited both columns (except the Title), and by pressing Control/Shift/F3 and selected Left Column, so that the cells are named with the Name in the cell. I then highlighted the second column (the column with the named cells) including the Title, and pressed Control/Shift/F3 and selecting Top Row, I now have a list of names called 'Names' On Sheet 2, I have put in a two columns before the actual 'To Do' item, and in the first column I created a drop down box using Data Validation and (by pressing F3), selected 'Names' as the list, so now the drop down box shows me the list of names (of the second column) on Sheet 1. Now, in the second column on Sheet 2, I have entered =HYPERLINK('[To Do's.xls]Sheet1!'&A2,A2) - the first A2 referring to the Name in the drop down box, and the second A2 being the friendly name that is displayed in the second column. I don't get an error message on formatting, so I don't believe my problem is in the formula. Also, when I select a name in the first column from the drop down box, the second column shows the same name highlighted and underlined (indicating it is a hyperlink), but when I click the hyperlink I get the 'Cannot Open the specified file' error message. How I learned to do this (I am by no means an Excel programmer) is from the following YouTube videos: Excel Magic Trick #138: Names Cell If Name in Cell - YouTube Excel Magic Trick #139: HYPERLINK function & Drop Down List - YouTube One other bit of info that may be affecting this; occassionally upon opening any Workbook, Excel will open up, but before the actual file opens, I get a notification saying something about Lookup (unfortunately, I have just tried now and can't get it to repeat), and after I click okay, it proceeds on opening the file and everything works fine (until the problem above). Not sure if this has anything to do with my problem above, but thought I should mention. Sorry for all the detail, but not being a programmer, I'm not sure what info you do/do not need. Rick ![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |