Creating Long SQL strings with Arrays

If you've ever created an long SQL string for a Pivot Table or some other reason, you will have come across Excel's limitations in this area. For example, you cannot have too many line continuations ( _ ). Also, if you have used the macro recorder while getting External Data through MS Query, there is some limitation there too. It will not actually record all the SQL, but cuts it off half way through.

The way the recorder works when getting data for a Pivot Table is interesting and I thought I'd use it in my code in a different way. It creates an array of arrays like this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 28/06/2005 by A
'

'
    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = Array(Array( _
        "ODBC;DSN=Xtreme Sample Database 2003;DBQ=C:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports\Samples\Database\xtreme." _
        ), Array("mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT Employee.`Employee ID`, Employee.`Supervisor ID`, Employee.`Last Name`, Employee.`First Name`, Employee.Position, Employee.`Birth Date`, Employee.`Hire Date`, Employee.`Home Phone`, Employee.Ex" _
        , _
        "tension, Employee.Photo, Employee.Notes, Employee.`Reports To`, Employee.Salary, Employee.SSN, Employee.`Emergency Contact First Name`, Employee.`Emergency Contact Last Name`, Employee.`Emergency Cont" _
        , _
        "act Relationship`, Employee.`Emergency Contact Phone`" & Chr(13) & "" & Chr(10) & "FROM Employee Employee" _
        )
        .CreatePivotTable TableDestination:="[Book1]Sheet1!R3C1", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    End With
    
    'do stuff with pivot tables
    
End Sub

It's a bit messy so I thought I'd clean it up. I used the array idea by adding bits of the string into the next element of a growing array, and then joining the array using the Join Function (opposite of Split).

Sub Macro1Redo()

    Dim ssql As String
    Dim sConn As String

    'create the array string
    Call AddToArray(ssql, "SELECT Employee.Employee ID, Employee.Supervisor ID, Employee.Last Name,")
    Call AddToArray(ssql, "Employee.First Name, Employee.Position, Employee.Birth Date, ")
    Call AddToArray(ssql, "Employee.Hire Date,Employee.Home Phone, Employee.Extension,")
    Call AddToArray(ssql, "Employee.Photo, Employee.Notes, Employee.Reports To,")
    Call AddToArray(ssql, "Employee.Salary, Employee.SSN, Employee.Emergency Contact First Name,")
    Call AddToArray(ssql, "Employee.Emergency Contact Last Name, Employee.Emergency Contact Relationship,")
    Call AddToArray(ssql, "Employee.Emergency Contact Phone ")
    Call AddToArray(ssql, "FROM Employee")
    
    'join the strings back together
    ssql = Join(ssql)
    
    Call AddToArray(sConn, "ODBC;DSN=Xtreme Sample Database 2003;")
    Call AddToArray(sConn, "DBQ=C:\Program Files\Microsoft Visual Studio .NET 2003\")
    Call AddToArray(sConn, "Crystal Reports\Samples\Database\xtreme.mdb;")
    Call AddToArray(sConn, "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")

    'join the strings back together
    sConn = Join(sConn)

    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = sConn
        .CommandType = xlCmdSql
        .CommandText = ssql
        .CreatePivotTable TableDestination:="[Book1]Sheet1!R3C1", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    End With
    
    'do stuff with pivot tables
    
End Sub


Sub AddToArray(myArray As Variant, arrayElement As Variant)

If Not IsArrayInitialized(myArray) Then
    ReDim myArray(0)
    myArray(0) = arrayElement
Else
    ReDim Preserve myArray(UBound(myArray) + 1)
    myArray(UBound(myArray)) = arrayElement
End If

End Sub