To get refreshed data from a Web site using Excel 97, save the site address (URL) for every new query in a text file with the extension .iqy.
To save an address in a text file as a query:
1. Open the Web site that contains the information. For example, open the site www.bloomberg.com, which includes a table of various currency exchange rates. The address of the page containing the table of currency exchange rates is http://www.bloomberg.com/
markets/index.html.
2. Copy the address by selecting it in the address box and pressing Ctrl+C.
3. Open a new text file in Windows by selecting Programs, Accessories, and then Notepad from the Start menu.
4. Press Ctrl+V to paste the address into the text file, and then save the file with the extension .iqy (be sure to use lowercase). For example, save the file with the name CrossCurrencyRates.iqy.
5. Open Excel 97, and from the Data menu, select Get External Data, and then Run Web Query.
6. In the Run Query dialog box, search for and select the CrossCurrencyRates.iqy text file.
7. In the Returning External Data to Microsoft Excel dialog box, click OK.
8. Wait a few seconds. The data is transferred from the Web site to the Excel sheet.
To refresh the Internet data (the Web site does not have to be open):
1. Select the cell in the sheet containing the data.
2. From the Data menu, select Refresh Data.
OR
Display the External Data toolbar and click the Refresh Data icon. To display the External Data toolbar, select one of the toolbars, right-click and select External Data, and click OK.
To automatically refresh the Internet data:
1. On the External Data toolbar, click the Data Range Properties icon.
2. Select the Refresh every option, and set the number of minutes between each refresh action.
3. Select the Refresh data on file open checkbox to automatically refresh the data when the file is opened
To run a saved query:
1. From the Data menu, select Get External Data, and then Run Web Query.
2. Select the saved query, and click Get Data.
Screenshot // Getting Continuously Refreshed Data from a Web Site in Excel 97
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
How could something as original, useful and easy as this one be not known till now. This is an excellent tip and will be very useful in my day-to-day work. I can't thank you enought.