Macro to retrieve info on web pages?

MrAsh

MrAsh

New Member
Joined
Nov 3, 2006
Messages
23
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)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
MrAsh,

Give this as try:
Code: 
Sub CopyInfo()
    Dim WB As Workbook
    Dim SH As Worksheet
    Dim SH1 As Worksheet
    Dim i As Long
    Dim IE As Object
    
    Set WB = ActiveWorkbook
    Set SH = WB.Sheets(1)
    Set SH1 = WB.Sheets(2)
    
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    For i = 1 To Range("A1", Range("A65536").End(xlUp)).Cells.Count
        IE.Navigate SH.Cells(i, 1).Value
        While IE.Busy
            DoEvents
        Wend
        SH1.Cells(i + 1, 1).Value = IE.Document.Forms(0).All.Item(4).InnerText
        SH1.Cells(i + 1, 2).Value = IE.Document.Forms(0).All.Item(10).InnerText
        SH1.Cells(i + 1, 3).Value = IE.Document.Forms(0).All.Item(13).InnerText
        SH1.Cells(i + 1, 4).Value = IE.Document.Forms(0).All.Item(16).InnerText
        SH1.Cells(i + 1, 5).Value = IE.Document.Forms(0).All.Item(19).InnerText
        SH1.Cells(i + 1, 6).Value = IE.Document.Forms(0).All.Item(23).InnerText
        SH1.Cells(i + 1, 7).Value = IE.Document.Forms(0).All.Item(27).InnerText
    Next i
    IE.Quit
End Sub
 
Upvote 0
Thanks very much for the suggestion, Ahnold, and so quick too!

It's *almost* doing it...it seems everything is doing what it should be with the exception of following the *actual* link that's in the cells.

Instead of going to here it's going to google and putting the name of the school in the search field, like this and then pasting bits of the google page (Web Images Groups News etc) into the second worksheet cells, rather than the Address/Headteacher/Telephone..etc.

So, like i said, it's *almost* doing it, but not quite, but we are close!
 
Upvote 0
What is the value in the cell that should link to your example?
Click to expand...

On the face of it, it's just linked text saying "Banbury School" (the rest are all schools too, linked in the same way) but it's linked to this rather long address

and the cell is formatted as 'General'. (Unfortunately the page(s) addresses can't be made any shorter, due to the way the site(s) operate.

Is that what you mean, Ahnold?

(apologies for screwing up the formatting of this thread with the long addy!)
[Edited by admin - apology accepted, and I just fixed it, anyway. :) I just made it a clickable link rather than display the entire url in the text itself]
 
Upvote 0
Change this line of code:
Code: 
IE.Navigate SH.Cells(i, 1).Value
To this:
Code: 
IE.Navigate SH.Cells(i, 1).Hyperlinks.Item(1).Address
 
Upvote 0
Ok, it fires up iE to the right page now, but then gets stuck and says;

Run-time error '91';

Object variable or With object variable not set


and when i click Debug, it highlights this line;

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


i really appreciate the time you're spending with me on this btw!
 
Upvote 0
OK....let's try this. Change this:
Code: 
While IE.Busy 
    DoEvents 
Wend
To this:
Code: 
Do
    DoEvents
Loop Until IE.ReadyState = 4
 
Upvote 0
Now, it comes up with the same error, and highlights the same line, but it did paste the info into worksheet2 for the first link, and opens the browser window for the second link.

We're so close i can almost taste it!
 
Upvote 0
MrAsh

The probable reason for this error is because all the pages
aren't formatted/structured in the same way.

You could 'hide' the error with On Error Resume Next.
 
Upvote 0
You must log in or register to reply here.

Similar threads

The Ruff Report
  • Question
Macro Help - Simple Copy and Paste Until Null Cell Reached
Replies
1
Views
259
The Ruff Report
The Ruff Report
R
  • Question
Macro help with loops and auto stop
Replies
0
Views
130
RaElla1
R
F
  • Question
A macro for fetching data from a web page source
Replies
0
Views
414
fafas
F
S
  • Question
Can a macro help create a user hierarchy?
Replies
7
Views
268
Akuini
Akuini
M
  • Question
Clipboard error when copying shape
Replies
4
Views
539
marc81
M

Forum statistics

Threads
1,215,949
Messages
6,127,880
Members
449,411
Latest member
AppellatePerson

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top