Fetching certain information from a webpage using VBA

[sourabh]

Hi,

I am trying to use VBA to get the number of news results on google news on certain keywords. I have more than a 1000 keywords that I wanted it to run for.

One way, I thought of doing is to use VBA to fetch the source code and extract from the source code, certain text I need.

This is the code that I found. It seems to work fine with many websites, but doesn't return results from certain website, specially http://news.google.com

I would appreciate any help. I am very new to VBA.

Function GetSourcePage(ByVal URL As String) As String
Dim IEapp As Object

Set IEapp = CreateObject("InternetExplorer.Application")

IEapp.Navigate URL

While IEapp.Busy
DoEvents
Wend

GetSourcePage = IEapp.document.DocumentElement.innerHTML

IEapp.Quit
Set IEapp = Nothing

End Function

 

[Norie]

That code does work and will return the HTML for the page.

However there might be other ways to do what you want, without having to parse out the data from a whole load of HTML.

Can you be more specific about what it is you want exactly?

You mention 'keywords', are those for searching with google or do you want to search for them on a particular news page?

 

[sourabh]

That code does work and will return the HTML for the page.

However there might be other ways to do what you want, without having to parse out the data from a whole load of HTML.

Can you be more specific about what it is you want exactly?

You mention 'keywords', are those for searching with google or do you want to search for them on a particular news page?

Thanks Norie for a quick reply. To be specific, I want to go on to the google news website, search for a keyword, and note down the number of results it displays, for example when I search "purdue university", it says "Results 1 – 10 of about 2,266 for purdue university". I want the 2,266 number in a excel cell next to the cell where it says "purdue University"

Google news seems to have easy links and instead of doing the whole process. We can directly use the link. (http://news.google.com/news/search?aq=f&pz=1&cf=all&ned=us&hl=en&q=purdue+university) and just change the last two words.

P.S.: that code works well, but it results in #Value! when I use google news website.

 

[sourabh]

Seems like the google's website source code is very complicated and when excel paste it in the cell, it is not able to keep in the text string and displays an error.

Perhaps there is a way to show only a part of the text/code.

I would really appreciate any help on this. I am very new to VBA.

 

[Norie]

This code works for me, returning the results stats.

Sub GetSearchNos()
Dim IEapp As Object
Dim doc As Object
Dim dvMain As Object
Dim divs As Object
Dim divRslts As Object
Dim strURL As String
Dim I As Long
 
    strURL = "http://news.google.com/news/search?aq=f&pz=1&cf=all&ned=us&hl=en&q=purdue+university"

    Set IEapp = CreateObject("InternetExplorer.Application")
 
    IEapp.Navigate strURL
 
    While IEapp.Busy
        DoEvents
    Wend

    IEapp.Visible = True
 
    Set doc = IEapp.document
 
    Set dvMain = doc.getelementbyid("main-wrapper")

    Set divs = dvMain.getelementsbytagname("DIV")

    Do
        Set divRslts = divs(I)
        I = I + 1
    Loop Until divRslts.classname = "results-stats" Or I > divs.Length
 
    MsgBox divRslts.innertext
 
    IEapp.Quit
 
    Set IEapp = Nothing
 
End Sub

It's pretty crude, uses a message box etc but it should show how this sort of thing can be done.

 

[sourabh]

This is great. Thanks Norie, I really appreciate your help into this.

 

[hi2selva]

Hi Norie, I have the similar requirement and tried to execute the above code but I am getting automation error while executing Set IEapp = CreateObject("InternetExplorer.Application"). Is there anything else I have to do prior to execute this piece of codes?

 

[ZigZagZ]

Sorry to resurrect this thread, but I'm trying to do almost exactly the same thing on a Mac (using Safari).

I have a list of text strings and I'd like to check the number of results for each one on a particular webpage and return those in the adjacent cells - is it possible to do this using VBA on a Mac?

 

[Norie]

I don't think it's possible, definitely not with the code posted here anyway as it's specifically for Internet Explorer.

You might want to search for code that doesn't automate IE, probably something involving XMLHTTP.