How do I Extrat The Value of the ID Element into Excel?

[drmingle]

<div id="resultStats">About 2,660,000,000 results<nobr>  (0.20 seconds) </nobr></div>

This is coming from the source of Google's home page (right under the search toolbar).

I essentially want to pull down the phrase "About 2,660,000,000 results".

Up to this point I have been using Excel's web query and recording it with a macro.

Any help would be appreciated...

 

[Scott Huish]

Excel Workbook
	A	B
1	About 2,660,000,000 results (0.20 seconds)*	About 2,660,000,000 results
Sheet1

 

[drmingle]

Hotpepper... thanks for the quick response.


However, I am actually trying to download this information from the internet, not really parse anything out.

 

[poolhall]

drmingle,

I think you need to save the webpage source code first, and then search for what you need in it. Here's the working example how to save the source code: http://www.mrexcel.com/forum/showpost.php?p=1692315&postcount=17

 

[Norie]

I can think you can do that you would either need to automate IE or use XMLHTTP.

If you look at the first part of the code in poolhall's link it shows you how to automate IE.

However you don't need to save the source code to a text file to get what you want.

You can just add some code after Set objDoc = ie.Document.

Option Explicit
 
Sub GetGoogleResultsStats()
Dim ie As Object, objDoc As Object, f As Object, fs As Object
Dim NoResults As String
Const strURL As String = "http://www.google.co.uk/#sclient=ps...q=f&aqi=g5&aql=&oq=&pbx=1&fp=8d5bc33c08a07e29"
 
    Set ie = CreateObject("internetexplorer.application")
 
    ie.Navigate strURL
    'Wait for page to load!
    Do
        If ie.ReadyState = 4 Then
            ie.Visible = False
            Exit Do
        Else
            DoEvents
        End If
    Loop
 
    Set objDoc = ie.Document
 
    NoResults = objDoc.getelementByID("resultStats").InnerText
 
    MsgBox NoResults

    Set objDoc = Nothing

    Set ie = Nothing

End Sub

Obviously replace the URL with a real one - I just copied what was in the address bar after searching for 'something'.

 

[drmingle]

Norie:

Thank you for the code. This is what I am looking for...

I am getting an "error code 91" on the line:

NoResults = objDoc.getElementById("resultStats").innerText

Is there a DLL that I should activate through the reference library?

 

[Norie]

That error has nothing to do with a DLL.

Did you substitute a valid URL for the one in the code?

The basic test of validity would be that the URL actually returns the result stats on the page.

If it doesn't then the code won't find an element with the ID 'resultStats', so there is no object to work with.

 

[drmingle]

I did put a proper URL in the code.

No results because it errored out (error 91).

Excel prompted me to debug.

 

[drmingle]

Norie & Poolhall:

I got the code to work.

Thanks a million.

 

[drmingle]

Const strURI As String = "http://www.google.com/#sclient=psy&...=1&bav=on.2,or.r_gc.r_pw.&fp=d69918eb06677aa8"

Is there a way to make "Const StrURI As String" a variable which pulls from an Excel filed (let's say A2 sheet1)?