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]

Ruddles, that post doesn't seem to do what the OP is requesting. The OP doesn't want a hyperlink to the I'm Feeling Lucky search function (which I gave him in post #2 of this thread), he wants the URL of the website that this I'm Feeling Lucky function redirects you to.

 

[mohanyathish]

Hi moonfish...

the output as desired....but i noticed that if there a delay in the result from IE, the URL from the previous result is taken...

for example...

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> N TV http://www.n-tv.de/
Zeit http://www.n-tv.de/
Kultur News http://www.kulturnews.de/knde/index.php
Radio Runde Hamm http://www.kulturnews.de/knde/index.php
Aargauer Zeitung http://www.aargauerzeitung.ch/
Abendblatt http://www.aargauerzeitung.ch/


I guess...by including a time delay we can stop the ghosting of results...

i will need your help for adding the time delay in the web query...

thanks...

 

[moonfish]

I thought I had the delay problem taken care of already. There's a "Do While IE.Busy". That's supposed to wait until the page has loaded (I think).

I tested the macro on your example list and it didn't come up with any problems. It returned http://www.radiorundehamm.de/

EDIT: replace 'IE.Busy' with 'IE.Readystate <> 4', see if that works.

 

[mohanyathish]

It seems the problem is when u have more than one search term....i usually have atleast 15 at a time i excel...but let me check and see if it works when the suggested change is done....

Once again Thanks for your effort....

 

[Ruddles]

Ruddles, that post doesn't seem to do what the OP is requesting. The OP doesn't want a hyperlink to the I'm Feeling Lucky search function (which I gave him in post #2 of this thread), he wants the URL of the website that this I'm Feeling Lucky function redirects you to.

I don't know what to say... when I tried it, it went to the first Web site okay. Did you try the suggestion in Post #12?

Does this not work for you: http://www.google.com/search?q=utility+fog&btnI=I'm+Feeling+Lucky?

 

[Ruddles]

It seems the problem is when u have more than one search term

You concatenate them with + signs:-

?q=[B][COLOR=blue]utility[/COLOR][COLOR=red]+[/COLOR][COLOR=#0000ff]fog[/COLOR][/B]&btnI=I'm+Feeling+Lucky

 

[moonfish]

Does this not work for you: http://www.google.com/search?q=utility+fog&btnI=I'm+Feeling+Lucky?

It does work, it takes me to the utility fog page. But he wants the cell in excel to display http://en.wikipedia.org/wiki/Utility_fog instead of the google search URL. He specified that in post #3.

Also, Mohanyathish:
There's no problem related to spaces. N TV, Kultur News and Aargauer Zeitung work without problems. I don't know what caused the problem you encountered so I don't know how to fix it.

The only possible explanation I can come up with for the cause of your problem is that the site was not available at the time.

I built in a check to see if there's two identical webpages in a row:

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.Readystate <> 4 or 
            Application.Wait DateAdd("s", 1, Now)
        Loop
    
        Cells(i, 2) = IE.Document.URL
        If i > 1 And Cells(i, 2) = Cells(i - 1, 2) Then
            With Cells(i, 2).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 255
            End With
        End If
    Next i
    
    IE.Quit
    
End Sub

Right now it only colors the cell red. If you want it to do something else (like retry), adjust the code as you see fit.

 

[Ruddles]

It does work... But he wants the cell in excel to display http://en.wikipedia.org/wiki/Utility_fog instead of the google search URL.

Ah, I see! I don't see how that can be done directly but I guess you could write a VBA function to open IE in the background, navigate to that link, see where it goes, and return the URL.

Let me ponder... any maybe cogitate...

 

[Ruddles]

If you pass the "I'm Feeling Lucky" link to this function, it will return the actual URL which it's linked to:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Function GetLinkedAddress(ByVal argURL As String) As [/FONT][FONT=Fixedsys]String[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Const READYSTATE_COMPLETE As Integer = 4
[/FONT]
[FONT=Fixedsys]  Dim objIE As InternetExplorer[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Set objIE = CreateObject("InternetExplorer.Application")
  
  With objIE
    .Visible = False
    .Silent = True
    .Navigate (argURL)
    Do Until .ReadyState = READYSTATE_COMPLETE
      DoEvents
    Loop
  End With
  
  GetLinkedAddress = objIE.LocationURL[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  objIE.Quit
  Set objIE = Nothing
  
End Function[/FONT]

MsgBox getlinkedaddress("[URL]http://www.google.com/search?q=utility+fog&btnI=I'm+Feeling+Lucky[/URL]")

 

[Ruddles]

Sorry, declare objIE as Object.

And also, actually, objIE.LocationName might be nicer than the URL.