Automated Search Results Returning Nothing

**Bill** · 10-06-2020, 02:21 AM

Hi,

Attached macro has an issue as I don't see any result, can you check please.

Thanks;
Ehab

**DocAElstein** · 10-07-2020, 02:42 AM

Hello Bill / Ehab
Welcome to ExcelFox

I don’t get any results either when I run your Sub SearchGoogle().
Could you explain what results you expect.
Could you also explain in a bit of detail what this macro is doing, or supposed to be doing.
How does it work, or how are you expecting it to work?
What is it supposed to do?
If you did not write the coding, then please tell us where you got it from.

It is rather difficult to help if you just tell us that there is an issue because you don’t see any results.
( Or at least its difficult for me to help without more information, as I am not familiar with coding of this form: It is not obvious to me what it is supposed to be doing or how).

Alan

**Bill** · 10-07-2020, 02:58 PM

Hi Alan

How kind you are to help me. Thank you very much.
I have some companies and I want to get their websites, " the first ULR from Google search". so I checked ExcelFox and I found what I need but unfortunately the macro doesn't work.
I got the macro from https://excelfox.com/forum/showthrea...sult-Using-VBA
Grateful for your support...

Thanks;
Ehab

**DocAElstein** · 10-08-2020, 03:17 AM

I am afraid I don’t think I can help further on this one. I am not familiar with coding of this sort.
I took a look at that Thread you referenced, - There it has already been mentioned by others that the macros are not working anymore..
Sorry
If no one else picks this thread up here, then you could try some of the other Excel forums, such as mrexcel.com or excelforum.com.
( Note: Most forums have a “cross post rule” , which basically means you should mention to everyone everywhere else that you post the same question )

Alan

**DocAElstein** · 10-11-2020, 12:06 PM

Hi
On second thoughts, I founds a very simple solution , based on a very simple way that I had used previously to get something given at a web site. ( There I was looking for the given IP address at a free site which determines your current IP address , https://excelfox.com/forum/showthrea...ll=1#post11672 )
The method I use is very simple. It is much simpler than the more complicated web scraping codings typically given: I expect there may be good reasons, many that I don't know about which mean that such complicated codings are needed. For example, in a different Google version to that my computer uses, the macro may not work. However if this is the case, and you are able to understand my method, then you may be able to modify the macro accordingly so that it works for you.

I have given some extended explanations to my method here: https://excelfox.com/forum/showthrea...ll=1#post14991

This is the final macro I have written for you:

Code:

Sub GoogleSearchURL()   '     https://excelfox.com/forum/showthread.php/2656-Automated-Search-Results-Returning-Nothing            https://excelfox.com/forum/showthread.php/973-Lookup-First-URL-From-Google-Search-Result-Using-VBA
 On Error GoTo Bed
Rem 0 Worksheets data range info
Dim Ws1 As Worksheet, Rng As Range
 Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim Lr1 As Long: Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row   '  ' Dynamically getting the last row in worksheet referenced by  Ws1    https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=11467&viewfull=1#post11467
    For Each Rng In Ws1.Range("A2:A" & Lr1 & "")
    '_1 First section get the long text string of the HTML coding of the internet Page
    '_1(i) get the long single text string
        With CreateObject("msxml2.xmlhttp")
'        .Open "GET", "https://www.google.com/search?q=Chandoo", False ' 'just preparing the request type, how and what type... "The True/False argument of the HTTP Request is the Asynchronous mode flag. If set False then control is immediately returns to VBA after Send is executed. If set True then control is returned to VBA after the server has sent back a response.
         .Open "GET", "https://www.google.com/search?q=" & Rng.Value & "", False
         'No extra info here for type GET
         .setRequestHeader bstrheader:="Ploppy", bstrvalue:="Poo"
         '.setRequestHeader bstrheader:="If-Modified-Since", bstrvalue:="Sat, 1 Jan 2000 00:00:00 GMT" '  https://www.autohotkey.com/boards/viewtopic.php?t=9554  ---   It will caching the contents of the URL page. Which means if you request the same URL more than once, you always get the same responseText even the website changes text every time. This line is a workaround : Set cache related headers.
         .send ' varBody:= ' No extra info for type GET. .send actually makes the request
            While .readyState <> 4: DoEvents: Wend ' Allow other processes to run while the web page loads. Think this is part of the True option
        Dim PageSrc As String: Let PageSrc = .responseText ' Save the HTML code in the (Global) variable. ': Range("P1").Value = PageSrc 'For me for a print out copy to text file etc.    The responseText property returns the information requested by the Open method as a text string
        End With
    ''_1(ii)  Optional secion  to put the text string into a text file , for ease of code developments
    'Dim FileNum2 As Long: Let FileNum2 = FreeFile(0)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
    'Dim PathAndFileName2 As String
    ' Let PathAndFileName2 = ThisWorkbook.Path & "\" & "Chandoo" & ".txt"   ' CHANGE TO SUIT
    ' Let PathAndFileName2 = ThisWorkbook.Path & "\" & Rng.Value & ".txt"   ' CHANGE TO SUIT ( use only one of these two similar code lines )
    ' Open PathAndFileName2 For Output As #FileNum2                         ' CHANGE TO SUIT  ' The text file will be made if not there, and if it is there and already contains data, then the data will be overwritten
    ' Print #FileNum2, PageSrc '
    ' Close #FileNum2
    '
    Rem proposed simple solution
    Dim posURL As Long
     Let posURL = InStr(1, PageSrc, "class=""fuLhoc ZWRArf"" href=""/url?q=", vbBinaryCompare)  '  This gives us the position of   class="fuLhoc ZWRArf" href="/url?q=    counting chjaracter from the start of the entire long string
    Dim PageSrcFromURL As String
     Let PageSrcFromURL = Mid(PageSrc, posURL + 35)   '   This effectively gives us a string starting at out wanted URL                        This string    class="fuLhoc ZWRArf" href="/url?q=    is 35 characters long, so we need to add that to get at out URL start position
    Dim posURLEnd As Long
     Let posURLEnd = InStr(1, PageSrcFromURL, "&amp;sa=U&amp;ved", vbBinaryCompare)             '  this tells us where   &amp;sa=U&amp;ved   starts, counting characters from the left
    Dim FirstURL As String
     Let FirstURL = Left(PageSrcFromURL, posURLEnd - 1)                                         ' We want the string up to one character less than where    &amp;sa=U&amp;ved    is
    
    Let Rng.Offset(0, 1).Value = FirstURL
    Next Rng
Exit Sub  '  Normal code error in the case of no errors
Bed:
 MsgBox prompt:=Err.Number & ":  " & Err.Description: Debug.Print Err.Number & ":  " & Err.Description
End Sub   ' Code end in the case of any error

This is the test data in your uploaded file, the Before:

_____ Workbook: GoogleLookupUrl (2).xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	NGO List	URL
2	ExcelFox
3	Chandoo
4	indianrailway
5	neustar
6	MrExcel
7	Ozgrid
8

Worksheet: Sheet1

If you run the macro above, Sub GoogleSearchURL() , then these are the After results:

_____ Workbook: GoogleLookupUrl (2).xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	NGO List	URL
2	ExcelFox	http://www.excelfox.com/forum/forum.php
3	Chandoo	https://chandoo.org/
4	indianrailway	http://www.indianrail.gov.in/
5	neustar	https://www.home.neustar/
6	MrExcel	https://www.mrexcel.com/
7	Ozgrid	https://www.ozgrid.com/
8

Worksheet: Sheet1

Alan

**Bill** · 10-11-2020, 03:27 PM

Hi Alan,

First, I wanted to send my thanks for all your assistance. Deep thanks Alan.

I tried to run this macro and I got attached issue, please check attached file. please if you can to help me to handle it.

FYI. I got another macro and give the first result from google but the result contain that the website + Title in the same cell.

So Is there ability to get the hyperlinks only as below.

Searched word is 3M

Current result:

Web results

3M Science. Applied to Life. 3M United Stateswww.3m.comwww.3m.com
Cached
3M applies science and innovation to make a real impact by igniting progress and inspiring innovation in lives and communities across the globe.
‎About 3M · ‎Products · ‎Covid-19 · ‎Personal Protective Equipment

Expected result from macro after the update:

https://www.3m.com/

Words cannot express how grateful I am for your help.

Thanks;
Bill

**DocAElstein** · 10-12-2020, 01:59 PM

Hi

Originally Posted by Bill

tried to run this macro and I got attached issue, please check attached file.

_ I assume you are talking about the macro I gave?
_ I have never seen the error, System Error H80070685 (-2147023179). The interface is unknown , before.
_ The attached file only has the macro Sub Gethits() in it. So I am not sure what the attached File is supposed to be do with that error issue?

I did a quick search on the internet and could not find anything specifically on that error. But similar errors seemed to be either something to do with internet or windows issues.
It sounds like something not directly related to the coding.

Originally Posted by Bill

... I got another macro and give the first result from google but the result contain that the website + Title in the same cell.
So Is there ability to get the hyperlinks only as below.
Searched word is 3M
Current result:

Web results

3M Science. Applied to Life. 3M United Stateswww.3m.comwww.3m.com
Cached
3M applies science and innovation to make a real impact by igniting progress and inspiring innovation in lives and communities across the globe.
‎About 3M • ‎Products • ‎Covid-19 • ‎Personal Protective Equipment

Expected result from macro after the update:

https://www.3m.com/. ..

The macro Sub Gethits() looks like the typical web scrapping coding.
As I mentioned, I don’t have much experience with such coding
That macro works for me and I also get the same results that you get.
I will take a guess that the macro is supposed to get those results. Where did you get that macro?

I expect that you will need to adjust this section to get the hyperlink.

Code:

        Set objResultDiv = html.getelementbyid("rso")
        Set var1 = html.getelementbyid("result-Stats")
        If Not var1 Is Nothing Then
            Cells(x, 2).Value = objResultDiv.Child.innerText
        Else
            Cells(x, 2).Value = "0"
        End If

But I do not know what changes are needed.

If I run my macro with this,
_____ Workbook: GoogleLookupUrl (2).xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	NGO List	URL
2	ExcelFox
3	Chandoo
4	indianrailway
5	neustar
6	MrExcel
7	Ozgrid
8	3M

Worksheet: Sheet1

Then I get these results
_____ Workbook: GoogleLookupUrl (2).xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	NGO List	URL
2	ExcelFox	http://www.excelfox.com/forum/forum.php
3	Chandoo	https://chandoo.org/
4	indianrailway	http://www.indianrail.gov.in/
5	neustar	https://www.home.neustar/
6	MrExcel	https://www.mrexcel.com/
7	Ozgrid	https://www.ozgrid.com/
8	3M	https://www.3m.com/

Worksheet: Sheet1

I don’t know why the macro does not work for you.

As I mentioned previously, I don’t know much about these sort of internet codings.

Alan

**Bill** · 10-14-2020, 06:31 PM

Hi Alan

How kind you are to help me. Thank you very much.
I don't know what the exact issue but I think it is in browser as the macro open IE page then give this issue.
If you can to let the macro browser like Chrome.
as I have a lot of macros deal with Internet with out any issue.

Thanks;
Bill

**DocAElstein** · 10-15-2020, 06:31 PM

Originally Posted by Bill

....I don't know what the exact issue but I think it is in browser as the macro open IE page then give this issue.
If you can to let the macro browser like Chrome.
as I have a lot of macros deal with Internet with out any issue....

Hi Bill,

That does not really make any sense, ( unless you are talking about the very first macro , Sub SearchGoogle() )
( We have alredy established that Sub SearchGoogle() does not work.

My macro, Sub GoogleSearchURL() , and the last one you uploaded, Sub Gethits() do not have anything to do with any browser. Those two macros do not open any browser.
Those two macros obtain the HTML source code via a code line of like
_______.ResponseText

You need to be more clear about what macros you are talking about. You need to be more clear generally in your replies. ( I will take a guess that your mother tongue is not English, and that you probably understand little, if anything, of what I am telling you….. )

Alan