dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] VBA script to search a text in google maps

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Resolved [RESOLVED] VBA script to search a text in google maps

    Dear All,
    Hope you are safe at home.

    I'm searching for a macro which can make my work easy and i can learn new things in macro.
    My Query is very simple.
    I have text list in column A text include company name with Location name (ex:"kfc 10003 new york") when i search this in google i can see a map box on right side, which i need to click on it copy the url and paste in column B. Again i wrote a formula in Column C which will separate lat and long value from url.
    My request is to get a macro which will search the text and paste the url only which has box location map on the right side of web page and paste in Column B.
    I apologize if my way of explaining is not good, I am in learning stage and will sure improve it.
    Please find attached screen print of my query.
    Thanks in advance.
    Attached Images Attached Images  

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,719

    Re: VBA script to search a text in google maps

    You have posted in the VB.NET forum. There is an Office forum for VBA questions. I have asked the mods to move this thread.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,415

    Re: VBA script to search a text in google maps

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: VBA script to search a text in google maps

    Code:
    wb.document.getelementbyid("lu_map").Click
    where wb.document is the internet explorer document containing the google search
    this is tested to work with the example posted in post #1

    of course you will need to modify this to fit with your search code, and wait for the search code to complete before trying to click the element

    note: this may be against googles t&cs
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: VBA script to search a text in google maps

    Hi,
    Thank you so much for the reply.

    is it possible to provide me the full code. Please don't mind i started learning VBA and this is for my personal learning.

    Regards

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: VBA script to search a text in google maps

    is it possible to provide me the full code
    if you post the code you have so far, to do the parts that are working, i can try to add to it for you
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: VBA script to search a text in google maps

    Code:
    Sub URL()
      Dim IE As InternetExplorer
      Dim IEDoc As HTMLDocument
      Dim FormGoogleCherche As HTMLInputElement
      Dim ElementByid As HTMLElementCollection
      Dim InputGoogleZoneTexte As HTMLInputElement
      Dim InputGoogleBouton As HTMLInputElement
      Dim strAddHotel As String
    
      Set IE = CreateObject("InternetExplorer.Application")
      IE.navigate "www.google.com"
      IE.Visible = True
      WaitIE IE
    
      Set IEDoc = IE.document
      Set InputGoogleZoneTexte = IEDoc.all("q")
    
      Sheets("shhet1").Select
      strAddHotel = Range("A1:A250").Value
    
      InputGoogleZoneTexte.Value = strAddHotel
      Set FormGoogleCherche = IEDoc.forms("f")
      FormGoogleCherche.submit
    
      Set ElementByid = IEDoc.getElementById("lu_map")
      ElementByid.Click
      
      Set IE = Nothing
      Set IEDoc = Nothing
    End Sub
    
    Sub WaitIE(IE As InternetExplorer)
       Do Until IE.readyState = READYSTATE_COMPLETE
          DoEvents
       Loop
    End Sub
    Last edited by si_the_geek; May 1st, 2020 at 05:31 PM. Reason: added Code tags

  8. #8

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: VBA script to search a text in google maps

    I'm trying to modify the above code:
    I am getting error as i know that above code has mistakes.
    My requirement was to search a text of range A1:A250 in google maps and get that URL in B1:B250.
    Please help me out with this...

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: VBA script to search a text in google maps

    you can test this, it was working ok for me, i sued the below search pattern in column A
    kfc 10003 new york
    diesel wodonga
    mtq wodonga
    dewars engines
    all searches found the intended result, though i would not want to guarantee that, but the 3rd one did not produce a map to click on

    Code:
    Range("b2:d5").Clear
    
    Set wb = CreateObject("internetexplorer.application")
    wb.Visible = True
    On Error Resume Next
    
    For Each cel In Range("a2:a5")
        wb.navigate2 "https://www.google.com/search?q=" & cel
        Do Until wb.readystate = 4: Loop
        Sleep 9
        wb.document.getelementbyid("lu_map").Click
        If Not Err.Number = 0 Then
    '        MsgBox "no picture"
            cel.Offset(, 1) = "no picture to click"
            Err.Clear
            Else
            Do Until wb.readystate = 4: Loop
            Sleep 2000
            tmp = wb.locationURL
            cel.Offset(, 1) = tmp
            pos = InStr(tmp, "@")
            pos2 = InStr(tmp, ",")
            pos3 = InStr(pos2 + 1, tmp, ",")
            Debug.Print cel.Row, pos, tmp
            cel.Offset(, 2) = Mid(tmp, pos + 1, pos2 - pos - 1)
            cel.Offset(, 3) = Mid(tmp, pos2 + 1, pos3 - pos2 - 1)
            tmp = ""
        End If
    Next
    wb.Quit
    Set wb = Nothing
    dimension all variables, i left that for you to do
    you will also need the API call for sleep
    Code:
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    you may need to adjust the delay times for the calls to sleep, if the second call is not long enough it will return the wrong URL
    you will also need to adjust the range of rows to iterate through
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: VBA script to search a text in google maps

    Hi Sir,

    Iam getting a error "user defined type not defined" while running the code.

    for the above code i consider Dim wb as InternetExplorer as user defined but still i am getting error.

    where do i need to update this line of code Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)"

    Sir, I apologize for asking again and again but I am knowing new things and learning by your help.

    Thank you.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: VBA script to search a text in google maps

    to dimension a variable as internet explorer would require a reference to internet explorer, or alternatively dimension the variable as object

    where do i need to update this line of code Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)"
    this line needs to be at the top of the module in the general section
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: VBA script to search a text in google maps

    Hi,

    Its working awesome!!

    But i have query regarding sleep method, when i gave sleep 2000 for 200 records the data was wrong. As you mentioned earlier we need to change the Range.
    My query is when I gave sleep15000 for 200 records still i can see some are not captured.

    Please help me out if there is any other alternate.
    Thank you for your help. "You are a excel Champ"

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: VBA script to search a text in google maps

    "You are a excel Champ"
    this actually has little to do with excel

    My query is when I gave sleep15000 for 200 records still i can see some are not captured.
    the length of time to sleep is dependent, on multiple factors including speed of computer and speed of internet, which may vary at different times of day especially at this time with many kids being home and doing home schooling and playing internet games all day, also the complexity of the google search, there is no magic formula to work it out, the length of sleep is per row, so if most rows are returning a valid response you may need to try the ones with wrong data manually, so you can watch what happens for them

    you could put the sleep time value into a cell so that you can adjust it without changing the code all the time

    most times checking the readystate of the internet explorer object is usually a good method, but had no affect on the code at all
    you could possibly try do while wb.busy: loop, i did not test with this and sometimes it works a bit differently to wb.readystate
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    New Member
    Join Date
    Apr 2020
    Posts
    7

    Re: VBA script to search a text in google maps

    Hi,
    Is it possible to capture the text which is below to the Image Box..
    Please help me out with this.
    Image attached for your reference.Name:  ex2.jpg
Views: 19
Size:  25.9 KB

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,852

    Re: VBA script to search a text in google maps

    Is it possible to capture the text which is below to the Image Box.
    you can test this addition to the original code
    Code:
    Set cls = wb.document.getelementsbyclassname("YhemCb")
    s = cls(cls.Length - 1).innertext
    note that some of the searches i tested with most only returned 1 element to the collection, but one returned 2 and you really wanted the second, so that is what the code does, that being the case some may return different numbers of elements to the collection, so you need to do extensive testing, to make sure you want the innertext from the last item in the colledction
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width