QueryTable Helper Functions

These are functions written by Bill Manville for manipulating strings for use in Connection and CommandText. Those are properties of the QueryTable object and they are arguments to the Add method of the QueryTable object. Sometimes you need strings broken into an array and sometimes you don’t. I’m trying to figure out the when and the why behind that.

Use SplitToArray when you need to pass a long string (>255 characters?) to the Add method via an argument. You can use to pass shorter strings too.

Function SplitToArray(ST As String, Lump As Integer)
‘ break a long string up into an array with each element of size Lump
‘ don’t bother if string is not longer than Lump
 Dim A()
  Dim I As Integer
  If Len(ST) < = Lump Then
    SplitToArray = ST
  Else
    ReDim A(1 To Len(ST) Lump + 1)
    For I = 1 To Len(ST) Lump + 1
      A(I) = Mid(ST, 1 + (I – 1) * Lump, Lump)
    Next
    SplitToArray = A()
  End If
End Function

Example:

Sheet1.QueryTables.Add _
    SplitToArray(sConnection, 255), _
    Sheet1.Range(“A1”), _
    SplitToArray(sSQL, 255)

Use Flatten when you have an array of strings and you need to replace some text in it.

Function Flatten(V) As String
‘ convert an array of strings into one long string
 Dim I As Integer
  If IsArray(V) Then
    For I = LBound(V) To UBound(V)
      Flatten = Flatten & V(I)
    Next
  Else
    Flatten = V
  End If
End Function

Example:

qt.Connection = SplitToArray(Replace(Flatten(arrConnString), “WHERE x=1”, “WHERE x=2”), 255)

I’ve asked Microsoft about this array situation although I suspect one of you will tell me what’s going on in a comment.

Posted in Uncategorized

One thought on “QueryTable Helper Functions

  1. I’ve been using a similar function for external pivot query connect strings based on MSKB article 269619. In this case, the function parses the connection and sql strings into 127- instead of 255-character substrings… According VBA help, only external PivotTables and PivotCaches return an array for the SourceData Property — “Each row consists of an SQL connection string with the remaining elements as the query string, broken down into 255-character segments.”

Leave a Reply

Your email address will not be published. Required fields are marked *