Create new tab for each google search by selenium in excel VBA











up vote
2
down vote

favorite
1












I am trying to do google search based on some data in column A in Sheet1 .. and I need each cell content to be opened in new tab and do the search for that cell
example: A1 has the word 'flower' so I expect to create a tab and navigate to google the do a search for that 'flower' then the next cell and so on
and each search to be in a new tab
Here's my try



Sub Test()
Dim bot As New ChromeDriver
Dim Keys As New Keys

bot.Get "https://www.google.com"
'search for items in column A

bot.ExecuteScript "window.open(arguments[0])", "https://www.google.com"
bot.SwitchToNextWindow
End Sub


I also tried that part



bot.FindElementById("gsr").SendKeys Range("A1").Value
bot.SendKeys bot.Keys.Enter
bot.SwitchToNextWindow


But I couldn't create a new tab










share|improve this question




























    up vote
    2
    down vote

    favorite
    1












    I am trying to do google search based on some data in column A in Sheet1 .. and I need each cell content to be opened in new tab and do the search for that cell
    example: A1 has the word 'flower' so I expect to create a tab and navigate to google the do a search for that 'flower' then the next cell and so on
    and each search to be in a new tab
    Here's my try



    Sub Test()
    Dim bot As New ChromeDriver
    Dim Keys As New Keys

    bot.Get "https://www.google.com"
    'search for items in column A

    bot.ExecuteScript "window.open(arguments[0])", "https://www.google.com"
    bot.SwitchToNextWindow
    End Sub


    I also tried that part



    bot.FindElementById("gsr").SendKeys Range("A1").Value
    bot.SendKeys bot.Keys.Enter
    bot.SwitchToNextWindow


    But I couldn't create a new tab










    share|improve this question


























      up vote
      2
      down vote

      favorite
      1









      up vote
      2
      down vote

      favorite
      1






      1





      I am trying to do google search based on some data in column A in Sheet1 .. and I need each cell content to be opened in new tab and do the search for that cell
      example: A1 has the word 'flower' so I expect to create a tab and navigate to google the do a search for that 'flower' then the next cell and so on
      and each search to be in a new tab
      Here's my try



      Sub Test()
      Dim bot As New ChromeDriver
      Dim Keys As New Keys

      bot.Get "https://www.google.com"
      'search for items in column A

      bot.ExecuteScript "window.open(arguments[0])", "https://www.google.com"
      bot.SwitchToNextWindow
      End Sub


      I also tried that part



      bot.FindElementById("gsr").SendKeys Range("A1").Value
      bot.SendKeys bot.Keys.Enter
      bot.SwitchToNextWindow


      But I couldn't create a new tab










      share|improve this question















      I am trying to do google search based on some data in column A in Sheet1 .. and I need each cell content to be opened in new tab and do the search for that cell
      example: A1 has the word 'flower' so I expect to create a tab and navigate to google the do a search for that 'flower' then the next cell and so on
      and each search to be in a new tab
      Here's my try



      Sub Test()
      Dim bot As New ChromeDriver
      Dim Keys As New Keys

      bot.Get "https://www.google.com"
      'search for items in column A

      bot.ExecuteScript "window.open(arguments[0])", "https://www.google.com"
      bot.SwitchToNextWindow
      End Sub


      I also tried that part



      bot.FindElementById("gsr").SendKeys Range("A1").Value
      bot.SendKeys bot.Keys.Enter
      bot.SwitchToNextWindow


      But I couldn't create a new tab







      excel vba excel-vba selenium web-scraping






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 19 at 20:30









      QHarr

      27.1k81839




      27.1k81839










      asked Nov 19 at 20:07









      YasserKhalil

      1,0981312




      1,0981312
























          1 Answer
          1






          active

          oldest

          votes

















          up vote
          1
          down vote



          accepted










          Try the following. You need to target the search box for text input.



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          .FindElementByCss("[title=Search]").SendKeys arr(i)
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub




          Using a timed loop to find the element:



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Const MAX_WAIT_SEC As Long = 5
          Dim ele As Object, t As Date
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          t = Timer
          Do
          DoEvents
          On Error Resume Next
          Set ele = .FindElementByCss("[title=Search]")
          On Error GoTo 0
          If Timer - t > MAX_WAIT_SEC Then Exit Do
          Loop While ele Is Nothing

          If Not ele Is Nothing Then
          ele.SendKeys arr(i)
          Else
          Exit Sub
          End If
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub





          share|improve this answer























          • Thank you very much Mr. QHarr. I have encountered an error at this line .FindElementByCss("[title=Search]").SendKeys arr(i) .. Element not found for CSS
            – YasserKhalil
            Nov 19 at 20:38












          • Works for me but perhaps you need a loop for it to be present. I will write something now and add in.
            – QHarr
            Nov 19 at 20:46










          • Can you also confirm that in your local version which opens that [title=Search] works. Select the search box and right click inspect element then check it has an attribute title with value Search. It is possible your local version may have something different. In that is the case, please share the HTML for that element.
            – QHarr
            Nov 19 at 20:50






          • 1




            I used this line bot.SendKeys bot.keys.Enter after the sendkeys and it worked well. Thank you very much. You're a legend
            – YasserKhalil
            Nov 19 at 21:40






          • 1




            No worries. Sorry I didn't know if you just wanted to input the value or initiate the search which is why I simply left as input.
            – QHarr
            Nov 19 at 21:52











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53381891%2fcreate-new-tab-for-each-google-search-by-selenium-in-excel-vba%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          1
          down vote



          accepted










          Try the following. You need to target the search box for text input.



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          .FindElementByCss("[title=Search]").SendKeys arr(i)
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub




          Using a timed loop to find the element:



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Const MAX_WAIT_SEC As Long = 5
          Dim ele As Object, t As Date
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          t = Timer
          Do
          DoEvents
          On Error Resume Next
          Set ele = .FindElementByCss("[title=Search]")
          On Error GoTo 0
          If Timer - t > MAX_WAIT_SEC Then Exit Do
          Loop While ele Is Nothing

          If Not ele Is Nothing Then
          ele.SendKeys arr(i)
          Else
          Exit Sub
          End If
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub





          share|improve this answer























          • Thank you very much Mr. QHarr. I have encountered an error at this line .FindElementByCss("[title=Search]").SendKeys arr(i) .. Element not found for CSS
            – YasserKhalil
            Nov 19 at 20:38












          • Works for me but perhaps you need a loop for it to be present. I will write something now and add in.
            – QHarr
            Nov 19 at 20:46










          • Can you also confirm that in your local version which opens that [title=Search] works. Select the search box and right click inspect element then check it has an attribute title with value Search. It is possible your local version may have something different. In that is the case, please share the HTML for that element.
            – QHarr
            Nov 19 at 20:50






          • 1




            I used this line bot.SendKeys bot.keys.Enter after the sendkeys and it worked well. Thank you very much. You're a legend
            – YasserKhalil
            Nov 19 at 21:40






          • 1




            No worries. Sorry I didn't know if you just wanted to input the value or initiate the search which is why I simply left as input.
            – QHarr
            Nov 19 at 21:52















          up vote
          1
          down vote



          accepted










          Try the following. You need to target the search box for text input.



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          .FindElementByCss("[title=Search]").SendKeys arr(i)
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub




          Using a timed loop to find the element:



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Const MAX_WAIT_SEC As Long = 5
          Dim ele As Object, t As Date
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          t = Timer
          Do
          DoEvents
          On Error Resume Next
          Set ele = .FindElementByCss("[title=Search]")
          On Error GoTo 0
          If Timer - t > MAX_WAIT_SEC Then Exit Do
          Loop While ele Is Nothing

          If Not ele Is Nothing Then
          ele.SendKeys arr(i)
          Else
          Exit Sub
          End If
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub





          share|improve this answer























          • Thank you very much Mr. QHarr. I have encountered an error at this line .FindElementByCss("[title=Search]").SendKeys arr(i) .. Element not found for CSS
            – YasserKhalil
            Nov 19 at 20:38












          • Works for me but perhaps you need a loop for it to be present. I will write something now and add in.
            – QHarr
            Nov 19 at 20:46










          • Can you also confirm that in your local version which opens that [title=Search] works. Select the search box and right click inspect element then check it has an attribute title with value Search. It is possible your local version may have something different. In that is the case, please share the HTML for that element.
            – QHarr
            Nov 19 at 20:50






          • 1




            I used this line bot.SendKeys bot.keys.Enter after the sendkeys and it worked well. Thank you very much. You're a legend
            – YasserKhalil
            Nov 19 at 21:40






          • 1




            No worries. Sorry I didn't know if you just wanted to input the value or initiate the search which is why I simply left as input.
            – QHarr
            Nov 19 at 21:52













          up vote
          1
          down vote



          accepted







          up vote
          1
          down vote



          accepted






          Try the following. You need to target the search box for text input.



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          .FindElementByCss("[title=Search]").SendKeys arr(i)
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub




          Using a timed loop to find the element:



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Const MAX_WAIT_SEC As Long = 5
          Dim ele As Object, t As Date
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          t = Timer
          Do
          DoEvents
          On Error Resume Next
          Set ele = .FindElementByCss("[title=Search]")
          On Error GoTo 0
          If Timer - t > MAX_WAIT_SEC Then Exit Do
          Loop While ele Is Nothing

          If Not ele Is Nothing Then
          ele.SendKeys arr(i)
          Else
          Exit Sub
          End If
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub





          share|improve this answer














          Try the following. You need to target the search box for text input.



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          .FindElementByCss("[title=Search]").SendKeys arr(i)
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub




          Using a timed loop to find the element:



          Option Explicit
          Public Sub Test()
          Dim bot As ChromeDriver, keys As New keys, arr(), ws As Worksheet, i As Long
          Const MAX_WAIT_SEC As Long = 5
          Dim ele As Object, t As Date
          Set bot = New ChromeDriver
          Set ws = ThisWorkbook.Worksheets("Sheet1") '<==Adjust to your sheet
          arr = Application.Transpose(ws.Range("A1:A3")) '<== Adjust to your range

          With bot
          .Start "Chrome"
          .get "https://google.com/"
          For i = LBound(arr) To UBound(arr)
          If Not IsEmpty(arr(i)) Then
          If i > 1 Then
          .ExecuteScript "window.open(arguments[0])", "https://google.com/"
          .SwitchToNextWindow
          End If
          t = Timer
          Do
          DoEvents
          On Error Resume Next
          Set ele = .FindElementByCss("[title=Search]")
          On Error GoTo 0
          If Timer - t > MAX_WAIT_SEC Then Exit Do
          Loop While ele Is Nothing

          If Not ele Is Nothing Then
          ele.SendKeys arr(i)
          Else
          Exit Sub
          End If
          End If
          Next
          End With
          Stop '<==Delete me later
          End Sub






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 19 at 20:49

























          answered Nov 19 at 20:26









          QHarr

          27.1k81839




          27.1k81839












          • Thank you very much Mr. QHarr. I have encountered an error at this line .FindElementByCss("[title=Search]").SendKeys arr(i) .. Element not found for CSS
            – YasserKhalil
            Nov 19 at 20:38












          • Works for me but perhaps you need a loop for it to be present. I will write something now and add in.
            – QHarr
            Nov 19 at 20:46










          • Can you also confirm that in your local version which opens that [title=Search] works. Select the search box and right click inspect element then check it has an attribute title with value Search. It is possible your local version may have something different. In that is the case, please share the HTML for that element.
            – QHarr
            Nov 19 at 20:50






          • 1




            I used this line bot.SendKeys bot.keys.Enter after the sendkeys and it worked well. Thank you very much. You're a legend
            – YasserKhalil
            Nov 19 at 21:40






          • 1




            No worries. Sorry I didn't know if you just wanted to input the value or initiate the search which is why I simply left as input.
            – QHarr
            Nov 19 at 21:52


















          • Thank you very much Mr. QHarr. I have encountered an error at this line .FindElementByCss("[title=Search]").SendKeys arr(i) .. Element not found for CSS
            – YasserKhalil
            Nov 19 at 20:38












          • Works for me but perhaps you need a loop for it to be present. I will write something now and add in.
            – QHarr
            Nov 19 at 20:46










          • Can you also confirm that in your local version which opens that [title=Search] works. Select the search box and right click inspect element then check it has an attribute title with value Search. It is possible your local version may have something different. In that is the case, please share the HTML for that element.
            – QHarr
            Nov 19 at 20:50






          • 1




            I used this line bot.SendKeys bot.keys.Enter after the sendkeys and it worked well. Thank you very much. You're a legend
            – YasserKhalil
            Nov 19 at 21:40






          • 1




            No worries. Sorry I didn't know if you just wanted to input the value or initiate the search which is why I simply left as input.
            – QHarr
            Nov 19 at 21:52
















          Thank you very much Mr. QHarr. I have encountered an error at this line .FindElementByCss("[title=Search]").SendKeys arr(i) .. Element not found for CSS
          – YasserKhalil
          Nov 19 at 20:38






          Thank you very much Mr. QHarr. I have encountered an error at this line .FindElementByCss("[title=Search]").SendKeys arr(i) .. Element not found for CSS
          – YasserKhalil
          Nov 19 at 20:38














          Works for me but perhaps you need a loop for it to be present. I will write something now and add in.
          – QHarr
          Nov 19 at 20:46




          Works for me but perhaps you need a loop for it to be present. I will write something now and add in.
          – QHarr
          Nov 19 at 20:46












          Can you also confirm that in your local version which opens that [title=Search] works. Select the search box and right click inspect element then check it has an attribute title with value Search. It is possible your local version may have something different. In that is the case, please share the HTML for that element.
          – QHarr
          Nov 19 at 20:50




          Can you also confirm that in your local version which opens that [title=Search] works. Select the search box and right click inspect element then check it has an attribute title with value Search. It is possible your local version may have something different. In that is the case, please share the HTML for that element.
          – QHarr
          Nov 19 at 20:50




          1




          1




          I used this line bot.SendKeys bot.keys.Enter after the sendkeys and it worked well. Thank you very much. You're a legend
          – YasserKhalil
          Nov 19 at 21:40




          I used this line bot.SendKeys bot.keys.Enter after the sendkeys and it worked well. Thank you very much. You're a legend
          – YasserKhalil
          Nov 19 at 21:40




          1




          1




          No worries. Sorry I didn't know if you just wanted to input the value or initiate the search which is why I simply left as input.
          – QHarr
          Nov 19 at 21:52




          No worries. Sorry I didn't know if you just wanted to input the value or initiate the search which is why I simply left as input.
          – QHarr
          Nov 19 at 21:52


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53381891%2fcreate-new-tab-for-each-google-search-by-selenium-in-excel-vba%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Wiesbaden

          Marschland

          Dieringhausen