Hello,
I am extracting roughly 2,500 different sets of data from the same main website but different variations. I have a VBA code that successfully connects a sample size, but when trying to do all 2,500 at once Excel will timeout and not be able to extract the data at all. Is there a way to extract one set of data end that connection and move to the next?
Here is the code.
Sub ExtractExternalData()
Dim x As Long, lastRow As Long
With Sheets("Date")
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For x = 1 To lastRow
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x
' On Error Resume Next
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & .Cells(x, 1) _
, Destination:=Range("A2"))
.Name = "/boxesetc/2016/B04030KCA2016.htm"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next
End With
End Sub
Thank you in advance.
Bookmarks