Create new tab for each google search by selenium in excel VBA
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
add a comment |
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
add a comment |
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
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
excel vba excel-vba selenium web-scraping
edited Nov 19 '18 at 20:30
QHarr
30.8k81941
30.8k81941
asked Nov 19 '18 at 20:07
YasserKhalilYasserKhalil
1,2411315
1,2411315
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
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 '18 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 '18 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 '18 at 20:50
1
I used this linebot.SendKeys bot.keys.Enter
after the sendkeys and it worked well. Thank you very much. You're a legend
– YasserKhalil
Nov 19 '18 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 '18 at 21:52
|
show 1 more comment
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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
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 '18 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 '18 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 '18 at 20:50
1
I used this linebot.SendKeys bot.keys.Enter
after the sendkeys and it worked well. Thank you very much. You're a legend
– YasserKhalil
Nov 19 '18 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 '18 at 21:52
|
show 1 more comment
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
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 '18 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 '18 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 '18 at 20:50
1
I used this linebot.SendKeys bot.keys.Enter
after the sendkeys and it worked well. Thank you very much. You're a legend
– YasserKhalil
Nov 19 '18 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 '18 at 21:52
|
show 1 more comment
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
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
edited Nov 19 '18 at 20:49
answered Nov 19 '18 at 20:26
QHarrQHarr
30.8k81941
30.8k81941
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 '18 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 '18 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 '18 at 20:50
1
I used this linebot.SendKeys bot.keys.Enter
after the sendkeys and it worked well. Thank you very much. You're a legend
– YasserKhalil
Nov 19 '18 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 '18 at 21:52
|
show 1 more comment
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 '18 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 '18 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 '18 at 20:50
1
I used this linebot.SendKeys bot.keys.Enter
after the sendkeys and it worked well. Thank you very much. You're a legend
– YasserKhalil
Nov 19 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 21:52
|
show 1 more comment
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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