Mixing
Cannot navigate in random order a range of URLs with VBA
0
I have a range of cells containing URLs
I navigate to them using a for loop like the following to get data from them. So I want to add some randomness
With ie
For j = 2 To 50
mylink = wks.Cells(j, 2).Value
.Visible = True
.Navigate mylink
'rest of code
Next
End with
How can I pick a cell with a url navigate to it and go to the next random cell without open them again?
thank you and a happy new year
excel vba excel-vba web-scraping
asked Jan 2 at 12:58
Nikos Nikos
1398
add a comment |
0
I have a range of cells containing URLs
I navigate to them using a for loop like the following to get data from them. So I want to add some randomness
With ie
For j = 2 To 50
mylink = wks.Cells(j, 2).Value
.Visible = True
.Navigate mylink
'rest of code
Next
End with
How can I pick a cell with a url navigate to it and go to the next random cell without open them again?
thank you and a happy new year
excel vba excel-vba web-scraping
asked Jan 2 at 12:58
Nikos Nikos
1398
add a comment |
0
0
0
I have a range of cells containing URLs
I navigate to them using a for loop like the following to get data from them. So I want to add some randomness
With ie
For j = 2 To 50
mylink = wks.Cells(j, 2).Value
.Visible = True
.Navigate mylink
'rest of code
Next
End with
How can I pick a cell with a url navigate to it and go to the next random cell without open them again?
thank you and a happy new year
excel vba excel-vba web-scraping
asked Jan 2 at 12:58
Nikos Nikos
1398
I have a range of cells containing URLs
I navigate to them using a for loop like the following to get data from them. So I want to add some randomness
With ie
For j = 2 To 50
mylink = wks.Cells(j, 2).Value
.Visible = True
.Navigate mylink
'rest of code
Next
End with
How can I pick a cell with a url navigate to it and go to the next random cell without open them again?
thank you and a happy new year
excel vba excel-vba web-scraping
excel vba excel-vba web-scraping
asked Jan 2 at 12:58
Nikos Nikos
1398
asked Jan 2 at 12:58
Nikos Nikos
1398
asked Jan 2 at 12:58
Nikos Nikos
1398
asked Jan 2 at 12:58
Nikos Nikos
1398
asked Jan 2 at 12:58
Nikos Nikos
1398
1398
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
You could try something like the following. This will generate a random number between 2 and 50, test if it has been used before (by comparing against a dictionary of used values) and then navigate. Although, I'm not entirely sure why you'd want to do this.
Dim UsedNumber As Object
Dim RndNumber As Long, MinNumber as long, MaxNumber as long
Dim j As Long
MinNumber = 2
MaxNumber = 50
Set UsedNumber = CreateObject("Scripting.Dictionary")
With ie
.Visible = True
For j = MinNumber To MaxNumber
Do
RndNumber = Int((MaxNumber - MinNumber + 1) * Rnd + MinNumber)
Loop While UsedNumber.exists(RndNumber)
UsedNumber.Add RndNumber, RndNumber
Debug.Print RndNumber
mylink = wks.Cells(RndNumber, 2).Value
.Navigate mylink
Next j
End With
answered Jan 2 at 13:08
TomTom
6,56511843
Because my guess is that the site has a mechanism that watch if I navigate to the same pages in the same order
– Nikos
Jan 2 at 13:10
Fair enough, if you're creating a Scraper personally I'd use Python and Scrapy for this instead of VBA
– Tom
Jan 2 at 13:13
I have more questions for python :) like if can i export what i get to excel that is why i stay for now with vba
– Nikos
Jan 2 at 13:16
1
You can use Python to generate a csv file fairly easily or there are Python Modules which will create a Workbook. Either of which you could then open in Excel
– Tom
Jan 2 at 13:18
1
Move the .Visible out of the loop and use .navigate2 would be useful.
– QHarr
Jan 2 at 19:54
|
show 4 more comments
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
});
}
});
draft saved
draft discarded
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%2f54006830%2fcannot-navigate-in-random-order-a-range-of-urls-with-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
0
You could try something like the following. This will generate a random number between 2 and 50, test if it has been used before (by comparing against a dictionary of used values) and then navigate. Although, I'm not entirely sure why you'd want to do this.
Dim UsedNumber As Object
Dim RndNumber As Long, MinNumber as long, MaxNumber as long
Dim j As Long
MinNumber = 2
MaxNumber = 50
Set UsedNumber = CreateObject("Scripting.Dictionary")
With ie
.Visible = True
For j = MinNumber To MaxNumber
Do
RndNumber = Int((MaxNumber - MinNumber + 1) * Rnd + MinNumber)
Loop While UsedNumber.exists(RndNumber)
UsedNumber.Add RndNumber, RndNumber
Debug.Print RndNumber
mylink = wks.Cells(RndNumber, 2).Value
.Navigate mylink
Next j
End With
answered Jan 2 at 13:08
TomTom
6,56511843
Because my guess is that the site has a mechanism that watch if I navigate to the same pages in the same order
– Nikos
Jan 2 at 13:10
Fair enough, if you're creating a Scraper personally I'd use Python and Scrapy for this instead of VBA
– Tom
Jan 2 at 13:13
I have more questions for python :) like if can i export what i get to excel that is why i stay for now with vba
– Nikos
Jan 2 at 13:16
1
You can use Python to generate a csv file fairly easily or there are Python Modules which will create a Workbook. Either of which you could then open in Excel
– Tom
Jan 2 at 13:18
1
Move the .Visible out of the loop and use .navigate2 would be useful.
– QHarr
Jan 2 at 19:54
|
show 4 more comments
0
You could try something like the following. This will generate a random number between 2 and 50, test if it has been used before (by comparing against a dictionary of used values) and then navigate. Although, I'm not entirely sure why you'd want to do this.
Dim UsedNumber As Object
Dim RndNumber As Long, MinNumber as long, MaxNumber as long
Dim j As Long
MinNumber = 2
MaxNumber = 50
Set UsedNumber = CreateObject("Scripting.Dictionary")
With ie
.Visible = True
For j = MinNumber To MaxNumber
Do
RndNumber = Int((MaxNumber - MinNumber + 1) * Rnd + MinNumber)
Loop While UsedNumber.exists(RndNumber)
UsedNumber.Add RndNumber, RndNumber
Debug.Print RndNumber
mylink = wks.Cells(RndNumber, 2).Value
.Navigate mylink
Next j
End With
answered Jan 2 at 13:08
TomTom
6,56511843
Because my guess is that the site has a mechanism that watch if I navigate to the same pages in the same order
– Nikos
Jan 2 at 13:10
Fair enough, if you're creating a Scraper personally I'd use Python and Scrapy for this instead of VBA
– Tom
Jan 2 at 13:13
I have more questions for python :) like if can i export what i get to excel that is why i stay for now with vba
– Nikos
Jan 2 at 13:16
1
You can use Python to generate a csv file fairly easily or there are Python Modules which will create a Workbook. Either of which you could then open in Excel
– Tom
Jan 2 at 13:18
1
Move the .Visible out of the loop and use .navigate2 would be useful.
– QHarr
Jan 2 at 19:54
|
show 4 more comments
0
0
0
You could try something like the following. This will generate a random number between 2 and 50, test if it has been used before (by comparing against a dictionary of used values) and then navigate. Although, I'm not entirely sure why you'd want to do this.
Dim UsedNumber As Object
Dim RndNumber As Long, MinNumber as long, MaxNumber as long
Dim j As Long
MinNumber = 2
MaxNumber = 50
Set UsedNumber = CreateObject("Scripting.Dictionary")
With ie
.Visible = True
For j = MinNumber To MaxNumber
Do
RndNumber = Int((MaxNumber - MinNumber + 1) * Rnd + MinNumber)
Loop While UsedNumber.exists(RndNumber)
UsedNumber.Add RndNumber, RndNumber
Debug.Print RndNumber
mylink = wks.Cells(RndNumber, 2).Value
.Navigate mylink
Next j
End With
answered Jan 2 at 13:08
TomTom
6,56511843
You could try something like the following. This will generate a random number between 2 and 50, test if it has been used before (by comparing against a dictionary of used values) and then navigate. Although, I'm not entirely sure why you'd want to do this.
Dim UsedNumber As Object
Dim RndNumber As Long, MinNumber as long, MaxNumber as long
Dim j As Long
MinNumber = 2
MaxNumber = 50
Set UsedNumber = CreateObject("Scripting.Dictionary")
With ie
.Visible = True
For j = MinNumber To MaxNumber
Do
RndNumber = Int((MaxNumber - MinNumber + 1) * Rnd + MinNumber)
Loop While UsedNumber.exists(RndNumber)
UsedNumber.Add RndNumber, RndNumber
Debug.Print RndNumber
mylink = wks.Cells(RndNumber, 2).Value
.Navigate mylink
Next j
End With
answered Jan 2 at 13:08
TomTom
6,56511843
edited Jan 2 at 20:06
answered Jan 2 at 13:08
TomTom
6,56511843
answered Jan 2 at 13:08
TomTom
6,56511843
answered Jan 2 at 13:08
TomTom
6,56511843
6,56511843
Because my guess is that the site has a mechanism that watch if I navigate to the same pages in the same order
– Nikos
Jan 2 at 13:10
Fair enough, if you're creating a Scraper personally I'd use Python and Scrapy for this instead of VBA
– Tom
Jan 2 at 13:13
I have more questions for python :) like if can i export what i get to excel that is why i stay for now with vba
– Nikos
Jan 2 at 13:16
1
You can use Python to generate a csv file fairly easily or there are Python Modules which will create a Workbook. Either of which you could then open in Excel
– Tom
Jan 2 at 13:18
1
Move the .Visible out of the loop and use .navigate2 would be useful.
– QHarr
Jan 2 at 19:54
|
show 4 more comments
Because my guess is that the site has a mechanism that watch if I navigate to the same pages in the same order
– Nikos
Jan 2 at 13:10
Fair enough, if you're creating a Scraper personally I'd use Python and Scrapy for this instead of VBA
– Tom
Jan 2 at 13:13
I have more questions for python :) like if can i export what i get to excel that is why i stay for now with vba
– Nikos
Jan 2 at 13:16
1
You can use Python to generate a csv file fairly easily or there are Python Modules which will create a Workbook. Either of which you could then open in Excel
– Tom
Jan 2 at 13:18
1
Move the .Visible out of the loop and use .navigate2 would be useful.
– QHarr
Jan 2 at 19:54
Because my guess is that the site has a mechanism that watch if I navigate to the same pages in the same order
– Nikos
Jan 2 at 13:10
Because my guess is that the site has a mechanism that watch if I navigate to the same pages in the same order
– Nikos
Jan 2 at 13:10
Fair enough, if you're creating a Scraper personally I'd use Python and Scrapy for this instead of VBA
– Tom
Jan 2 at 13:13
Fair enough, if you're creating a Scraper personally I'd use Python and Scrapy for this instead of VBA
– Tom
Jan 2 at 13:13
I have more questions for python :) like if can i export what i get to excel that is why i stay for now with vba
– Nikos
Jan 2 at 13:16
I have more questions for python :) like if can i export what i get to excel that is why i stay for now with vba
– Nikos
Jan 2 at 13:16
1
1
You can use Python to generate a csv file fairly easily or there are Python Modules which will create a Workbook. Either of which you could then open in Excel
– Tom
Jan 2 at 13:18
You can use Python to generate a csv file fairly easily or there are Python Modules which will create a Workbook. Either of which you could then open in Excel
– Tom
Jan 2 at 13:18
1
1
Move the .Visible out of the loop and use .navigate2 would be useful.
– QHarr
Jan 2 at 19:54
Move the .Visible out of the loop and use .navigate2 would be useful.
– QHarr
Jan 2 at 19:54
|
show 4 more comments
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.
draft saved
draft discarded
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%2f54006830%2fcannot-navigate-in-random-order-a-range-of-urls-with-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
