Excel VBA QueryTables TEXT vs URL Connections

Question

I have the following two pieces of code for extracting a large table from a web service, one for a URL connection:

With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Cells(1,1))
    .PostText = ""
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = False
    .WebSingleBlockTextImport = True
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    .WorkbookConnection.Delete
End With

And one for a TEXT connection:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL, Destination:=Cells(1,1))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    .WorkbookConnection.Delete
End With

Because I sometimes need to send parameters to the web service (either by PostText or in a long URL) the URL connection is more suitable for my purposes. However, for the same data set from the same web service (no parameters in this case), the refresh consistently takes 21 seconds with the URL connection, and only 12 seconds with the TEXT connection.

Is there a reason why the TEXT connection is so much faster? And is there anything I can do about the relative slowness of the URL connection?

I would expect that pulling down a text file (the `Text` parameter) from the web should be faster than a URL query. You may have better luck just making an XMLHTTP post request and parsing the results. — David Zemens, Jan 20 '14 at 18:17
@DavidZemens, I did this originally but it took even longer - around 60 seconds for the same file. — BBaxter, Jan 20 '14 at 18:22

Excel VBA QueryTables TEXT vs URL Connections

0 Answers0