Long Web Query Timeout By Web Server

kwanm

New Member
Joined
Mar 24, 2010
Messages
3
Hello All,

I seem to be timing out the server I'm running a rather long web query on. My database requires the web query to the site be run a little over 7500 times, however, I believe the site locks out my connection at around 5700 because it assumes its under a flood attack. Is there a solution around this? Perhaps a delay function in the code? Or an alternate code that will continually retry the web query until connectivity is re-established?

I've only attached the pertinent part of my code. Certain declarations of variables are stated in other parts of the code and thereby not included.

Any help you can offer is greatly appreciated. Thanks.

Code:
    Dim Firstrow As Long
    
    Firstrow = Range("J" & Rows.Count).End(xlUp).Row
    
    For m = Firstrow To Lastrow
    SEC = Cells(m, "c").Value
    BLK = Cells(m, "d").Value
    LOT = Cells(m, "f").Value
    CA = Cells(m, "g").Value
    UNIT = Cells(m, "h").Value
    Name = "SEC " & SEC & "BLK " & BLK & "LOT " & LOT & "CA " & CA & "UNIT " & UNIT
        If Len(CA) = 0 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=0&txtBldg=&txtCondo=&txtYear=2012"
        ElseIf Len(CA) = 1 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA000" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        ElseIf Len(CA) = 2 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA00" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        ElseIf Len(CA) = 3 Then
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA0" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        Else
            URLString = "URL;http://www.nassaucountyny.gov/mynassauproperty/inventory.jsp?txtSection=" & SEC & "&txtBlock=" & BLK & "&txtLot=" & LOT & "&txtSuffix=U&txtBldg=CA" & CA & "&txtCondo=" & UNIT & "&txtYear=2012"
        End If
        
        ActiveWorkbook.Worksheets.Add.Name = "Temp"
        With ActiveSheet.QueryTables.Add(Connection:=URLString, Destination:=Range("$A$1"))
            .Name = Name
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = True
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = False
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "1"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    
        Range("B4").Copy
        Sheets("NCTRE2009txsale For Database").Range("J" & m).PasteSpecial
        Application.DisplayAlerts = False
        Sheets("Temp").Delete
    
    Next m
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,279
Members
414,440
Latest member
Kim0204

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