Hi,
Attached macro has an issue as I don't see any result, can you check please.
Thanks;
Ehab
Printable View
Hi,
Attached macro has an issue as I don't see any result, can you check please.
Thanks;
Ehab
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
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
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
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:
This is the test data in your uploaded file, the Before: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, "&sa=U&ved", vbBinaryCompare) ' this tells us where &sa=U&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 &sa=U&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
_____ Workbook: GoogleLookupUrl (2).xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col NGO List URL ExcelFox Chandoo indianrailway neustar MrExcel Ozgrid
If you run the macro above, Sub GoogleSearchURL() , then these are the After results:
_____ Workbook: GoogleLookupUrl (2).xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col NGO List URL ExcelFox http://www.excelfox.com/forum/forum.php Chandoo https://chandoo.org/ indianrailway http://www.indianrail.gov.in/ neustar https://www.home.neustar/ MrExcel https://www.mrexcel.com/ Ozgrid https://www.ozgrid.com/
Alan
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
Hi
_ I assume you are talking about the macro I gave?Quote:
Originally Posted by Bill
_ 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.
The macro Sub Gethits() looks like the typical web scrapping coding.Quote:
Originally Posted by Bill
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.
But I do not know what changes are needed.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
If I run my macro with this,
_____ Workbook: GoogleLookupUrl (2).xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col NGO List URL ExcelFox Chandoo indianrailway neustar MrExcel Ozgrid 3M
Then I get these results
_____ Workbook: GoogleLookupUrl (2).xls ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col NGO List URL ExcelFox http://www.excelfox.com/forum/forum.php Chandoo https://chandoo.org/ indianrailway http://www.indianrail.gov.in/ neustar https://www.home.neustar/ MrExcel https://www.mrexcel.com/ Ozgrid https://www.ozgrid.com/ 3M https://www.3m.com/
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
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
Hi Bill,Quote:
Originally Posted by 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