Macro to retrieve info on web pages?

[MrAsh]

Hi all,

Hope someone can help with something i'm trying to achieve which will save me bags of time if i can get it to work. What i'm trying to do is this;

i have a long list of websites, and each website has information i want to extract. Here's an example of one of them.

What i'd like to do ideally is to have a macro that will go to the first cell (say A1) where the hyperlink is, open up iE, see the "Address:" line, copy it and paste it into A1 on worksheet2, then to read the next line "Headteacher:" and copy that and paste into B2 on worksheet2...and so on and so on until it reaches the last ("DFES no:") and stops.

WIth that completed it should move onto the next hyperlink in the next cell - which will be B2 on worksheet1, and repeat the process again, until it finally reaches an empty cell and runs out of hyperlinks to follow (in this case it'll be around A260).

i've tried creating a macro by just recording and doing it, and i can get it to open up a browser, copy one line and paste it, but i can't get it to move on and repeat the process for each item of data.

The websites and data change, but each page always follows the same format, btw.

i've been through newsgroups, microsoft's knowledge base and loads of excel tips sites, but i can't a solution find anywhere. If anyone could show me how, or point me in a direction where i could learn how i'd be very grateful as it'll save me literally hours of boring copying and pasting.

Many thanks!

(i'm using Excel 2002 SP3 and WinXP with iE7)

 

[Ahnold]

Try to run it again and when it debugs wait a couple of seconds and press the F5 key. What happens when you do this?

 

[MrAsh]

i added the On Error Resum Next and now it whizzes through and puts all (apart from the postcodes oddly) the info into sheet 2. Fantastic!!

Thank you so much, Ahnold, and you Norie, i'm so pleased! You've not only saved me an incredible amount of time and effort, but made sure i can impress my new boss too! Perfect timing as well, as it's time to go home now

Next, what i'd like to do next is find out the best way i can learn some of this VBA stuff so i don't have to keep bothering you all...or as much perhaps, anyway

Thanks again!

 

[Ahnold]

I didn't even catch that it wasn't copying the postcodes.
Change this line:

SH1.Cells(i + 1, 1).Value = IE.Document.Forms(0).All.Item(4).InnerText

To this:

SH1.Cells(i + 1, 1).Value = IE.Document.Forms(0).All.Item(4).InnerText _
            & IE.Document.Forms(0).All.Item(6).InnerText

 

[MrAsh]

Aye, that did it. i now have all the info *and* the postcodes too.

Nice one, Ahnold. i am eternally grateful for your 1337 excel skills and all of your hard work!

i'd like to get to know VBA better as i'm sure there'll be more occasions where this sort of thing will come in useful, so can you recommend a book, or a good starting point from which to learn..?

Many thanks again!