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










share|improve this question



























    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










    share|improve this question

























      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










      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Jan 2 at 12:58









      Nikos Nikos

      1398




      1398
























          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





          share|improve this answer


























          • 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











          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


















          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





          share|improve this answer


























          • 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
















          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





          share|improve this answer


























          • 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














          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





          share|improve this answer















          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






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 2 at 20:06

























          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



















          • 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




















          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














          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





















































          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

          'app-layout' is not a known element: how to share Component with different Modules

          android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

          WPF add header to Image with URL pettitions [duplicate]