Excel VBA QueryTables TEXT vs URL Connections

BBaxter picture BBaxter · Jan 20, 2014 · Viewed 13.1k times · Source

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?

Answer