Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 compatibility issue - speed between cells | Excel Discussion (Misc queries) | |||
Speed Issue | Excel Discussion (Misc queries) | |||
Speed Issue after updating cells | Excel Discussion (Misc queries) | |||
Calculation speed issue | Excel Discussion (Misc queries) | |||
Dynamic Ranges: Speed Issue | Excel Worksheet Functions |