Generate Query from Hyperlink and Extract a piece of data

**justinvalle** · 11-18-2009, 06:07 AM

I've got a sheet with about 120 hyperlinks. Each of those hyperlinks leads to the same type of webpage but has specific data related to that title. I need information about that title from the webpage that that link would lead me to, but I can't figure out how to grab it.

For Example:

Let's say these 2 hyperlinks are on my sheet-

Women's Health
Entertainment

and I know what each mag is rated (by users/voters), but I want to know how many users/voters that rating is based on. This info is available if I just click on the link, but I need it to appear on the same sheet, next to the hyperlink.

I'm thinking I should generate a web-query for each hyperlink, and extract the data from what will end up being the same table in every query, so how would I do that? Is there a better way?

Even a point in the right direction would be greatly appreciated!

**justinvalle** · 12-07-2009, 02:04 PM

I figured it out. The below code locates the "User ratings" text on a webpage.

Code:

Dim oSh As Worksheet
Dim hyperlink as string

Set oSh = Sheets("Temp Query")
hyperlink = http://www.blu-ray.com/movies/Transformers-Revenge-of-the-Fallen-Blu-ray/6216/

oSh.Cells.Clear

'this was code I was looking for'''''''
    With oSh.QueryTables.Add(hyperlink, oSh.Range("A1"))
        .BackgroundQuery = False
        .refresh
    End With
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Set ratingCell = oSh.Range("A1:Z75").Find(" user ratings", LookAt:=xlPart)
    Set reviewCell = oSh.Range("A1:Z75").Find("User reviews (", LookAt:=xlPart)

**Marzuk** · 12-07-2009, 10:37 PM

Just be aware that using QueryTables.add repeatedly on a worksheet is a bad idea. You will want to reuse the existing QueryTable and change the parameters + refresh to avoid that. Otherwise what is a 40k worksheet will be a couple MB soon.

**justinvalle** · 12-08-2009, 01:44 AM

Good point, Marzuk. I ran into that problem pretty early and just decided to delete the query each time before I created a new one. I edited above.

Grabbing a few strings from each of 100 web pages takes me a couple minutes, with the biggest factor probably being the webpage import/download. I love this new trick I found; can't believe it was so difficult for me to uncover online.

Thread: Generate Query from Hyperlink and Extract a piece of data

Thread Tools

Display

Generate Query from Hyperlink and Extract a piece of data

Re: Generate Query from Hyperlink and Extract a piece of data

Re: Generate Query from Hyperlink and Extract a piece of data

Re: Generate Query from Hyperlink and Extract a piece of data

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions