Inserting a Wait / Pause in a VBA loop to get around page request limit

[d0rian]

I use the VBA below to scrape 250 URLs that I have listed in a column on a sheet named "URLs" from a site (all identical, except with different company data). But they instituted some traffic management limit service (Cloudfront?) that will block access to the entire domain (from my IP, at least) after ~150 URLs, after which I need to wait 3-5 minutes before I can access any URL on the domain again.

My workaround was to just split my 250 URLs into 2 buckets of 125, copy the 1st 125 into the URL column, run VBA > wait 5 minutes > copy 2nd group of 125 into the URL column > run VBA. But to reduce # of clicks, how can I keep all 250 URLs into the source URL column, but just insert a 5 minute wait/pause after the code loops through the first 125?

Sub Loop_through()
    Dim h1 As Worksheet, h2 As Worksheet
    Dim u1 As Long, u2 As Long
    Dim MyUrl As String
    '
    Range("quote_all_import_cols").ClearContents
    Range(Range("wiperange").Value).ClearContents
    Application.ScreenUpdating = False
    Application.StatusBar = False
    Set h1 = Sheets("URLs")     'origin
    Set h2 = Sheets("Quotes")   'destiny
    '
    u1 = h1.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To u1
        MyUrl = h1.Cells(i, "A").Value
        Application.StatusBar = "import data : " & i - 1 & " of : " & u1 - 1
        u2 = h2.Range("A" & Rows.Count).End(xlUp).Row + 1
        With h2.QueryTables.Add(Connection:="URL;" & MyUrl, Destination:=h2.Range("A" & u2))
            .Name = "quotes.php?symbol=X"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "1"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        u3 = h2.Range("A" & Rows.Count).End(xlUp).Row
        h2.Range("P" & u2 & ":P" & u3).Value = MyUrl
    Next
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    MsgBox "End"
End Sub

 

[Dan_W]

Hi - if you want to be able to use your computer during that 5 minute period, you might want to consider the PAUSE routine I've set out at the bottom of my reply.

I suggest adding the following code just before the Next towards the end of the code:

        If ul MOD 125 = 0 then
           Debug.Print ul & " urls processed. Pausing for 5 minutes"
           PAUSE 300           ' The unit used by PAUSE is seconds, so 300 seconds = 5 mins
       End if

This tells VBA that every time the UL counter becomes divisible by 125 (i.e., every 125 times), it needs to PAUSE for 300 seconds. It will then proceed with the balance, but not that it will pause again at the 250th pass, and the 375th, and so. You can adjust it easily enough.

The end part (after End WIth) should now look like this:

           u3 = h2.Range("A" & Rows.Count).End(xlUp).Row
        h2.Range("P" & u2 & ":P" & u3).Value = MyUrl
        If ul MOD 125 = 0 then
           Debug.Print ul & " urls processed. Pausing for 5 minutes"
           PAUSE 300           ' The unit used by PAUSE is seconds, so 300 seconds = 5 mins
       End if
    Next
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    MsgBox "End"
End Sub

And you will need to add the PAUSE subroutine to your code.

Public Sub PAUSE(Period As Single)
    Dim T As Single
    T = Timer
    Do 
        DoEvents
    Loop While T + Period > Timer
End Sub

 

[d0rian]

End Sub[/CODE]

Thanks, I finally got around to trying this, and conceptually it makes sense, but when I run the code, it just immediately hangs, and the status bar stays frozen with the text "Import Data: 1 of : 275" (see attached image).

Typically (when the code runs properly), the status bar will tick up wards 1-of-275, 2-of-275, 3 etc etc...moving up around 1 per second. But with your added If ul MOD code, it just immediately hangs at 1 of 275. When I comment out those 4 lines, it runs properly again. Any idea what could be causing that?

Just to be clear, I added those 4 If-ul-MOD lines as shown, and then added a new, separate PAUSE macro at the top of my VBA module, as shown in the attached images. Did I get that all correct?

 

[Dan_W]

Hi. Thanks for the detailed description - it helps a lot in understanding (hopefully!) the source of the problem. Do you not also get the output "X URLs processed. Pausing for 5 minutes." in the Immediate Window (not the StatusBar)?

So the way the pausing trigger works is by using the modulus operator - MOD:

This tells VBA that every time the UL counter becomes divisible by 125 (i.e., every 125 times)

so 1 divided by 125 is 0.008, 2 divided by 125 is 0.016, and so on until 125. 125 divided by 125 is 0 - so at that point, the code tells VBA to pause for 300 second. As it transpires, 0 MOD 125 also equals 0, which explains why you're not progressing beyond the first URL....

I'm not sure what prompted me to use your variable u1 as the progress counter for the number of URLs processed, but looking at it now, that's clearly wrong - my apologies. Try replacing the u1 in my code with the i variable. So:

If i MOD 125 = 0 then
           Debug.Print i & " urls processed. Pausing for 5 minutes"

Hopefully that works (fingers crosssed) - please let me know if it doesn't.

 

[Dan_W]

Hi. I stumbled across this thread looking for something else... My apologies - I completely forgot to follow up with you. How are you and how did the code go?