A co-worker was working on a spreadsheet that is pulling sports team
data
Each schedule is on a different URL and as such he has to call the
function 30 times.
This is a slow process. Is there anyway to call multiple web queries
without having to initialize a new connection?
i've included the code in case there is something else in the code
that could be causing the slow-down.
thanks in advance
Function GetSchedule(Team As String) As Worksheet
Dim Dst As Worksheet
Dim DstRange As Range
Dim Name As String
Dim Connection As String
Set Dst = ActiveWorkbook.Worksheets.Add
Set DstRange = Dst.Range("A1")
'All Text
Dst.Cells.NumberFormat = "@"
Connection = "URL;
http://www.nhl.com/nhl/app?
service=page&page=TeamSchedule" & _
"&team=" & Team & "&season=" & Season
Name = "app?service=page&page=TeamSchedule&team=" & Team &
"&season=" & Season
With Dst.QueryTables.Add(Connection:=Connection,
Destination:=DstRange)
.Name = Name
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = TablesToGrab
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Set GetSchedule = Dst
End Function