title: Excel VBA: Looped Web Queries
body: <p>I have a list of 100,000 URLs that I need to parse via an API call. I've sorted them into a list of 600+ concatenated strings, each containing 200 URLs - ready to be parsed.</p>
  
  <p>I've written the code below to loop the process, places the returned information about the URLs in the last row of column C, one at a time. However, my loop seems to be broken and I don't know why (looking at it too long) but I suspect it's a rookie mistake. After doing the first two concatenated strings (400 URLs, it starts to rewrite the information from around row 200, processing only the first string.</p>
  
  <p>The code is below and any help will be greatly appreciated. Regrettably, I can't share the URL that I'm attempting to parse because it's a propriety system built by my employers and isn't for public use.</p>
  
  <pre><code>Sub APIDataProcess()
  
      Dim lURLsLastRow As Long
      Dim lDataSetLastRow As Long
      Dim rngURLDataSet As Range
      Dim sURLArray As String
      Dim lURLArrayCount As Long
      Dim rngArrayCell As Range
  
      lURLsLastRow = Cells(Rows.Count, 1).End(xlUp).Row
      lDataSetLastRow = Cells(Rows.Count, 3).End(xlUp).Row
  
      Set rngURLDataSet = Range("A1:A" &amp; lDataSetLastRow)
  
      lURLArrayCount = Range("B1").Value ' placeholder for count increments
      sURLArray = Range("A" &amp; lsURLArrayCount).Value
  
  
      For Each rngArrayCell In rngURLDataSet
  
          If lsURLArrayCount &lt;= lURLsLastRow Then
              With ActiveSheet.QueryTables.Add(Connection:="URL;http://test.test.org/test.php", Destination:=Range("C" &amp; lDataSetLastRow))
                  .PostText = "urls=" &amp; sURLArray
                  .RowNumbers = False
                  .FillAdjacentFormulas = False
                  .PreserveFormatting = True
                  .RefreshOnFileOpen = False
                  .BackgroundQuery = False
                  .RefreshStyle = xlOverwriteCells
                  .SavePassword = False
                  .SaveData = True
                  .AdjustColumnWidth = True
                  .RefreshPeriod = 0
                  .WebSelectionType = xlEntirePage
                  .WebFormatting = xlWebFormattingNone
                  .WebPreFormattedTextToColumns = True
                  .WebConsecutiveDelimitersAsOne = True
                  .WebSingleBlockTextImport = False
                  .WebDisableDateRecognition = False
                  .WebDisableRedirections = False
                  .Refresh BackgroundQuery:=False
              End With
              lURLArrayCount = lURLArrayCount + 1
              Range("B1").Value = lURLArrayCount
  
              Application.Wait Now + TimeValue("00:01:00")
  
          Else
              Exit Sub
  
          End If
  
      Next rngArrayCell
  
  End Sub
  </code></pre>