What is the best way to get data from website

[kgraphics]

Hi, I am new to vba, I am trying to get a data from bank website, what I am trying to get is the exchange rate for only one of the countries listed on the site, the link never changes, but the rates update every few minutes. What I am trying to do is during data entry the form will fill the rate txtbox on form load. So is there a way to search html code of a site and get the content in the cell next to the country name? I searched this form and google and came up empty.

This is the link I am trying to get data from.

http://www.rbcroyalbank.com/rates/cashrates.html

for example if I need to get the exchange rate for United States (USD) in the last sell of that row. it should be easy to identify because the html never changes. but how function in vba could I use to scan the html and search for the cell I need.

thanks

 

[sonof27]

I have put together a very quick and nasty access2000 db for you to have a look at. It has one form that contains a web control, a button, some text boxes for the data to be placed in and a country list (Although the list is limited to Australia and United States only you could easily add more). Select a country and press the button and it will open your web site in the web control and then grab the data your after. Hope it is of some help.

 

[kgraphics]

Wow! That is amazing! Thank you soo much, I am going to look at the code and try to understand how it is done when I get home. thank you soo much, I only need United States.

 

[sonof27]

Your welcome, glad I could help.

 

[kgraphics]

hi, is there a way I can set the web-browser hidden, I tried to set the visibility no but it doesn't work, I am trying to just get the rate in a variable and use it in calculations, so no need to see the site. Thanks

 

[sonof27]

You know I've never had the need to hide mine before, your right you can't set visible to false without an error. So I have reworked it a little and am re-posting it, hopefully the correct way.

What we have now is two forms, one contains the web control and is a sub form on the main form. The sub form has it's visible property set to false and it seems to work, the user does not see the web form.

I have left the old form there incase you still wish to see how it was originally.

 

[kgraphics]

Thank you very much, I am now reading the vba code reference to understand how it is done, it is a habit of mine to try and understand. Thanks, is there a tutorial online you know off that deals with this topic?

 

[sonof27]

Not that I'm aware of, but pleny of references out there if you google something like
vba webbrowser.Document

 

[kgraphics]

Thank you for your help, you saved me allot of time, to enter it into my database I had to do it manually each costumer. Thanks

 

[sonof27]

Your welcome.

I have had a bit more of a play with it to tidy it up a little, although when doing it this way you need a reference to Microsoft HTML Object Library, this file already has it. I have re-posted again for you to have a look.

 

[kgraphics]

Just when I thought I was starting to understand the first code, this code seems alot shorter than the other one. What I dont understand is, how are you getting to the td where the rate is? I know you are using the name of the country to search in the html but what I dont see is how you are counting the tables, tr tags and td where the the data is located? I did some tutorials of webbrower object but didn't see any tutorial for getting specific data in a td cell from a html. Anyways thanks, I dont think I will sleep tonight until I understand. I just hate not understanding.. I will let you know when all is clear.. thank you, I know enough to use the code for now

 

[kgraphics]

I think I got it, but not sure 100% correct me if I am wrong, you loop through crap which holds the html code from the site, when you reach the name of the country you change the value of the txtboxes to the content of the TD's td 1,2 and last td being the buy rate.. Am I reading it wrong?

 

[sonof27]

Yep your right in relation to the first version. It puts the HTML code of the page into the string crap and then manipulates that to find the country and subsequently the values of the type, buy and sell prices.

In the last version I just ran it a few times to figure out that your table happens to be table number 5 on the web page, you stated it never changes. It does this by finding the 5th, or 6th as it starts at 0, Table element within the HTML, no need to read it into a string. It then loops through the rows, the TD tags of that table, and finds your country value in column 0, the first column. It then grabs the innerHtml of column 1, 2 and 3, which should be your type, buy and sell prices.

Much smaller and much neater than the first version, however if another table is added before your table then you will need to change your code to suite.

 

[kgraphics]

Thanks for explaining, it doesn't work with access 2010. that is what I have, your file works because access runs it like the version it was made in, but if I try the same thing with access 2010 it doesn't work at all. Microsoft changed the webbrowser control completely I think? I tried few things after reading the online resource, but nothing seems to work. thanks you, my eyes are open now so I kind understand it, but I will need to learn about the new webbrowser object and how to use it.

 

[sonof27]

OK, good to know as we will be changing to access 2010 late next year. One more thing I will have to change.

 

[Peter Bellamy]

That is a really useful piece of code!
I don't know anything about html but sort of see how your code works.

Would this site be too complex to achieve the same thing?
http://www.x-rates.com/d/GBP/table.html

This site also enables you to choose the country you want to 'start' with (shown on RHS) which is just a page selection and would mean a selectable string part in the web link.

 

[sonof27]

Sorry for not responding earlier, labor day long week end here and I do not have access at home.

Your web site is a little more difficult to read as the tables are not as well structured. I have made some changes to my original program and re-loaded it for you to have a look at.

There is an option on your site to download as excel so I have taken advantage of that option and then read from that to get the data. This, I believe, would mean the user would also need to have excel if they wish to use it.

Again this was created in Access2000 with very little testing just as an example of how to get data from a web page. Hope it helps.