Google search using VBA

[qiyusi]

Hello all,

hope you guys doing great and keep healthy.

I found a code to do google search from Excel.
Basically put keyword on A column, then it will shown first result in B column.
It working fine, how ever, i curious to know how to modify the result, not show the text, but the link.

I have no idea how to change getelementsbytagname("cite")(0) so the result will be a link.
Appreciate if anyone can help.

here's the full code:

Please Login or Register  to view this content.

Thank you very much.

[vba_php]

are you using any libraries in VBA? there are many that are relevant:

- HTMLobjectLibrary
- firefox library
etc, etc...

furthermore, this was written a long time ago that covers almost anything basic when it comes to automating IE. but now that MS has forced people to switch to edge, some of what is written here has to be changed:

https://www.access-programmers.co.uk...ng-vba.176968/

you said something about a link. links in HTML are attached to <a> tags. the relevant attribute is: href. that is the standard in HTML 4.0 and 5.0. you can pull it by pointing to the < a > tag collection.

[Norie]

You could start by changing 'cite' to 'a'.

[qiyusi]

Hi,

Thank you for taking time reply my thread.
I didn't check about HTML object and firefox library, just copy paste the code and it working good.

I tried to change the attribute to "a", seem working great. the result is as i expected at the moment. I can pull link of google first result into Excel.

Just thinking for next move.
Open the link that i just got, then pull data by the right attribute.

from here

---------------------
For i = 2 To lastRow

url = "https://www.google.com/search?q=" & Cells(i, 1) & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

------------------

to here
For i = 2 To lastRow

url = Cells(i, 1)

------------------

Do i correct?

[vba_php]

well the first thing you need to correct is your query string syntax. if you have more than one query arg, then you need ""+"" signs between them. here is goog's protocol for searching strings:

Please Login or Register  to view this content.

etc, etc, etc....

other than that, are you asking anything else? can't tell.

[qiyusi]

You could start by changing 'cite' to 'a'.

Thank you. yea, "a" return with link as needed.

However i have run the data to 700 row, then it stuck. return with error 91.
does the problem from the firefox or google? i didn't change any code, so i am sure the code work at first.

well the first thing you need to correct is your query string syntax. if you have more than one query arg, then you need ""+"" signs between them. here is goog's protocol for searching strings:

Please Login or Register  to view this content.

etc, etc, etc....

other than that, are you asking anything else? can't tell.

What i mean is, if i want only to use link from the excel cell and remove "https://www.google.com/search?"
example in cell A2 : www.example.com

i modify the code to : url = Cells(i, 1) but doesn't work.

[vba_php]

upload ur file

[qiyusi]

By the way,

for previous file, to get google result is solved, it's just my internet connection is lost,causing error 91.


Here's second code. to pull single data from url.
Here's my current file, still get error : XMLHTTP.Open "GET", url, False

Thank you.

[vba_php]

I have ver 2016 excel. what do you have? I have 2 XML libs by default. v.3 and v.6. putting a ref to 6.0 causing a conflict and I don't have time to
debug it. Furthermore, apparently v.3, which is what i set to test your code, does not have the HTTP request object you are using. see image 1 below.
can you post a screenshot of what refs you have set? thanks.

[qiyusi]

Hi,

Thank you. I just realize that i just remove double quote on url =Cells(i,1) and XMLHTTP.Open "GET", url, False problem is gone.

I just don't know how determine element ID and tag to get "SIC Code 8049" on given url example.

[vba_php]

LOL. simple. always happens.

I just don't know how determine element ID and tag to get "SIC Code 8049" on given url example.

to get an element's value by capturing it's ID attribute, use javascript's native function:

Please Login or Register  to view this content.

does that work with the XML lib you are using? my guess is that it does, or something similar.

[qiyusi]

Yes it work.
Just confuse which element ID or tag to use. to get 8049 on attached screenshot.

[vba_php]

that's a SPAN silly! it's NOT an anchor tag! does it actually appear as a hyperlink on the screen? because there are NO A TAGS anywhere your screen shot. the span is inside an ordered list, encapsulated by an unordered list. that is a standard protocol for navigation bars and primary menus of business websites. the article I showed you earlier does not cover spans. try this first, if you have not:

https://www.google.com/search?q=vba+get+span+innerhtml'

there are many returns there. more than likely you can get it done. let us know if you can't get it. if you can't, post the CSS file's code that houses the class called text-sm md:text-base.

or, that may even represent a nest in CSS code. I don't use "":"" symbols, colon symbols, so I'm not sure.

[qiyusi]

Tried to search information as suggested.
Still no luck

[vba_php]

I don't think you can actually capture class content so as far as I know the only chance you've got to get it is to actually click on the link with your mouse and then get the URL from the resulting page but if it's got a token in it that's generated dynamically there's no way you can get it because it changes every time you click the link

[Norie]

vba_php

You don't need to set any references for the OP's original post to work.

[vba_php]

vba_php

You don't need to set any references for the OP's original post to work.

can you offer anything else? I set refs all the time in a variety of languages. why not help him/her out?

[qiyusi]

Hello,

Back to original thread.
I consider to close this thread, as the goal to have Excel VBA to get first result of Google is reached.

Thank you for vba_php for helping me

[vba_php]

Well if it was me that truly helps you out sure you're welcome :-)