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

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,635

Office Version

1. 365

Platform

1. Windows

• Apr 18, 2011

• #2

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

 

Upvote 0

D

drmingle

Board Regular

Joined

Oct 5, 2009

Messages

229

• Apr 18, 2011

• #3

Hotpepper... thanks for the quick response.


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

 

Upvote 0

poolhall

Active Member

Joined

Jan 9, 2009

Messages

350

• Apr 18, 2011

• #4

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

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Apr 18, 2011

• #5

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.

Rich (BB code):

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'.

 

Upvote 0

D

drmingle

Board Regular

Joined

Oct 5, 2009

Messages

229

• Apr 18, 2011

• #6

Norie:

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

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

Code:

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

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

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Apr 18, 2011

• #7

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.

 

Upvote 0

D

drmingle

Board Regular

Joined

Oct 5, 2009

Messages

229

• Apr 18, 2011

• #8

I did put a proper URL in the code.

No results because it errored out (error 91).

Excel prompted me to debug.

 

Upvote 0

D

drmingle

Board Regular

Joined

Oct 5, 2009

Messages

229

• Apr 18, 2011

• #9

Norie & Poolhall:

I got the code to work.

Thanks a million.

 

Upvote 0

D

drmingle

Board Regular

Joined

Oct 5, 2009

Messages

229

• Apr 18, 2011

• #10

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

Click to expand...

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

 

Upvote 0