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

d0rian

Active Member
Joined
May 30, 2015
Messages
293
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?

VBA Code:
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
709
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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:

VBA Code:
           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.

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

d0rian

Active Member
Joined
May 30, 2015
Messages
293
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?
 

Attachments

  • stays.JPG
    stays.JPG
    9.3 KB · Views: 4
  • public_.JPG
    public_.JPG
    17.2 KB · Views: 4
  • commented.JPG
    commented.JPG
    24.2 KB · Views: 4

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
709
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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.
 

Forum statistics

Threads
1,147,477
Messages
5,741,362
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top