How to get "i am feeling lucky" google results in excel

[mohanyathish]

HI...

can a VBA be done for getting only the URL using the "I am feeling lucky" feature of google...?

I have a list of search terms in excel....say...in column A...
i need the "I am feeling lucky" URL in column B...

Example....

column A column B
cnnmoney http://money.cnn.com/

 

[moonfish]

If you don't mind I'm going to borrow one or two things and insert them in my code from post #17 to get:

Private Sub FeelingLucky()
    Dim i As Long
    Dim IE As Object
         
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = False
        .Silent = True
        
        For i = 1 To WorksheetFunction.CountA(Range("A:A"))
            .Navigate "http://www.google.com/search?ie=UTF-8&oe=UTF-8&sourceid=navclient&gfns=1&q=" & Cells(i, 1)
            Do While .ReadyState <> 4
                DoEvents
            Loop
    
            Cells(i, 2) = .Document.URL
        Next i
    
        .Quit
    End With
End Sub

What's the difference between .Document.URL, .LocationURL and .LocationName?

 

[Ruddles]

If you don't mind I'm going to borrow one or two things and insert them in my code...

That's what MrExcel is all about!

What's the difference between .Document.URL, .LocationURL...

I don't know... I tried a few different pages and these always had the same value.

...and .LocationName?

That's the page title, presumably from the HTML <TITLE> tag.

 

[michaelbarry]

I was hoping someone would know how to do this. Since no one's responded, here's my attempt:

Private Sub FeelingLucky()
    Dim i As Long
    Dim IE As Object
         
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False
        
    For i = 1 To WorksheetFunction.CountA(Range("A:A"))
        IE.Navigate "http://www.google.com/search?ie=UTF-8&oe=UTF-8&sourceid=navclient&gfns=1&q=" & Cells(i, 1)
        Do While IE.Busy
            Application.Wait DateAdd("s", 1, Now)
        Loop
    
        Cells(i, 2) = IE.Document.URL
    Next i
    
    IE.Quit
    
End Sub

Put your keywords in column A, starting at A1. URLs will be placed in column B.

It's not particularly fast because it actually has to load the entire page in IE before grabbing the URL. However, it does seem to work.

I played around with this and got it to work as well. The VBA link you posted was very informative. Thanks again for the excel help and insight! This might be fun to play around with using the original poster's idea of getting results for "I'm feeling lucky"

 

[fparker61]

I was hoping someone would know how to do this. Since no one's responded, here's my attempt:

Private Sub FeelingLucky()
    Dim i As Long
    Dim IE As Object
        
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False
       
    For i = 1 To WorksheetFunction.CountA(Range("A:A"))
        IE.Navigate "http://www.google.com/search?ie=UTF-8&oe=UTF-8&sourceid=navclient&gfns=1&q=" & Cells(i, 1)
        Do While IE.Busy
            Application.Wait DateAdd("s", 1, Now)
        Loop
   
        Cells(i, 2) = IE.Document.URL
    Next i
   
    IE.Quit
   
End Sub

Put your keywords in column A, starting at A1. URLs will be placed in column B.

It's not particularly fast because it actually has to load the entire page in IE before grabbing the URL. However, it does seem to work.

The biggest problem is google will restrict repeated searches. I tried this with 5000 rows and it mostly stopped me from getting results.