Speed issue with Multilple Web Queries
 

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

Speed issue with Multilple Web Queries
 

You have .Refresh BackgroundQuery:=False. So the code is synchronous; it
waits for the query to finish before continuing.
If you set if to True, the code will continue whilst the query is
connecting.
Of course, you have to allow for the fact that there may not be data
available yet when you come to read it.

NickHK

wrote in message
ups.com...
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