Excel VBA: Looped Web Queries

Question

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.

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.

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.

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" & lDataSetLastRow)

    lURLArrayCount = Range("B1").Value ' placeholder for count increments
    sURLArray = Range("A" & lsURLArrayCount).Value


    For Each rngArrayCell In rngURLDataSet

        If lsURLArrayCount <= lURLsLastRow Then
            With ActiveSheet.QueryTables.Add(Connection:="URL;http://test.test.org/test.php", Destination:=Range("C" & lDataSetLastRow))
                .PostText = "urls=" & 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

score 0 · Answer 1 · answered Dec 22 '11 at 23:24

You probably solved your own problem long ago but since the question is still open I will have a go.

I assume the intention is that B1 is initially 1 and is then stepped after each row is processed. This would allow you to stop the macro and carry on from where you had got to on the previous run.

But you do not use B1 or lURLArrayCount like that. The range you examine is always A1 to Amax. You step lURLArrayCount and store it in B1 but its value is not used within the loop.

You set sURLArray outside the loop but use it within.

The loop is For Each rngArrayCell but you never use rngArrayCell.

You do not step lDataSetLastRow after a result has been added.

Excel VBA: Looped Web Queries

1 Answers1