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.
‘ 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:
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.
‘ 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:
I’ve asked Microsoft about this array situation although I suspect one of you will tell me what’s going on in a comment.
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.”