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]

Excel Workbook
	A	B
1	http://www.google.com/search?ie=UTF-8&oe=UTF-8&sourceid=navclient&gfns=1&q=	*
2	MrExcel	I'm feeling lucky!
Sheet2

Tested to work with spaces and special characters, it seems as though it replaces them automatically.

 

[mohanyathish]

Thats not what is thinking about...i need the hyperlink of "I am feeling lucky" result....

example...

For "MrExcel"...the hyperlink should be "http://www.mrexcel.com/" and not "<!--[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]-->http://www.google.com/search?ie=UTF-8&oe=UTF-8&sourceid=navclient&gfns=1&q=MrExcel"

I need the hyperlink directly in excel...i dont want to click on the link....

 

[repairman615]

Hello,​

Welcome to the board!​

Check out: <TABLE style="WIDTH: 230pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=307><COLGROUP><COL style="WIDTH: 230pt; mso-width-source: userset; mso-width-alt: 11227" width=307><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 230pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2245582 class=xl65 height=20 width=307>Mrexcel thread, post 12

<TABLE style="WIDTH: 230pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=307><COLGROUP><COL style="WIDTH: 230pt; mso-width-source: userset; mso-width-alt: 11227" width=307><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 230pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2245582 height=20 width=307></TD></TR></TBODY></TABLE>



​

</TD></TR></TBODY></TABLE>​

 

[moonfish]

Maybe someone who knows more about VBA interacting with websites can help you.

The link I provided is all I can do for you, sorry.

 

[mohanyathish]

I hope somebody gives me a solution....

 

[mohanyathish]

I tried doing it myself...but I am going nowhere with regard to that problem...can anybody please help.... atleast a hint would help....

 

[moonfish]

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.

 

[Ruddles]

I tried doing it myself...but I am going nowhere with regard to that problem...can anybody please help.... atleast a hint would help....

RepairMan615 gave you a link where this is discussed and I can confirm that the solution posted in that thread worked fine for me.

Did you give it a try?

Edited: Check Post #12.

 

[mohanyathish]

HI moonfish...

it is working...that was exactly what i was looking for...

thank you so much for your time and effort....