| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 43433 | 2004-03-14 14:02:00 | Any Excel or VBA Gurus | hillisp (4195) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 222572 | 2004-03-14 14:02:00 | Does anyone know how to access an htlm document from the internet from within Excel using VBA? The situation is I have a spreadsheet which preforms calculations based on exchange rates and inflation data. I have sourced the data from the intenet and at present I have just copied and manual formated it into a refrenece worksheet which the main fomula lookup. What I want to do is to automate the updating of the reference worksheet by reading the html file off the internet each month (when it is updated) and extracting the information I want into my referenece worksheet. I can handle the reading and extracting of the required data from a file. It is just how do you access the file when it is not actually on your harddrive but on the internet. Just replacing the path to the file with a "http:\\www......" type string doesn't apear to work. Any suggestions? |
hillisp (4195) | ||
| 222573 | 2004-03-14 19:31:00 | I remember looking at this problem in another language and IIRC you can't access the webpage directly to download it. What you could do though is create a script using php or perl or some other similar web based language which accesses the webpage and saves it to a folder on your harddrive etc and your program access it from there. I would have no idea on how you would implement this but it would be the easiest way to complete this. - David |
DangerousDave (697) | ||
| 222574 | 2004-03-14 20:27:00 | What version of Excel do you use? If you have 2002 you can go Data, Import External Data, New Web Query. Then browse to the website with the exchange rates on it and select the appropriate table. The data will then be inserted into excel. I think you still have to right click on the data to refresh it but there's probably a way to automate that. Let us know how you go. B. |
Barnabas (4562) | ||
| 222575 | 2004-03-15 06:21:00 | Thanks Barnabas -The "get external data" works in Excel 2000 as well. I have never used that function before so will have a play, but it looks like it will do what I want (with a little bit of VBA thrown in to reformat the data to how I want it). Thanks. |
hillisp (4195) | ||
| 222576 | 2004-03-15 10:35:00 | I have also managed to find a VB method to download a file from the internet to the harddrive (which also works in VBA from within Excel). you can then just read it in like any other text file. vbnet.mvps.org |
hillisp (4195) | ||
| 222577 | 2004-03-15 20:44:00 | The suggested Web Query is the simpler way to go. If you manually run the Data/GetExternalData/NewWebQuery on a new worksheet, with cell A1 selected, and import the web page / table into that sheet, the rate will be in a specific cell. This cell can be linked to a cell in your reference worksheet. Each subsequent time Data/RefreshData is executed in the worksheet it will re-import the web page - with a new rate data etc. the VBA code to refresh the external data in the worksheet is Range("a1").Select Selection.QueryTable.Refresh BackgroundQuery:=False HTH |
Russell D (18) | ||
| 1 | |||||