Excel 97 Array from MS Query, SQL PivotTables

[Quintios]

I recorded a macro of the creation of a pivot table. The results are understandable except for the Array that is used to describe the SQL needed for the query. Here's what I get:

    strSQL = Array("SELECT Count(UPI_Events.Index), UPI_Events.StartTime, UPI_Events.TagIndex, UPI_Events.TypeIndex, UPI_Tags.UnitIndex, UPI_Tags.Tag, UPI_Tags.Description, UPI_Units.SectionIndex, UPI_Sections.Section" & vbCrLf & _
        "F", "ROM `c:\UPI\UPI_DATA_UNIT2`.UPI_Events UPI_Events, `c:\UPI\UPI_DATA_UNIT2`.UPI_Sections UPI_Sections, `c:\UPI\UPI_DATA_UNIT2`.UPI_Tags UPI_Tags, `c:\UPI\UPI_DATA_UNIT2`.UPI_Units UPI_Units" & vbCrLf & "WHERE UPI_" _
        , "Events.TagIndex = UPI_Tags.TagIndex AND UPI_Sections.SectionIndex = UPI_Units.SectionIndex AND UPI_Tags.UnitIndex = UPI_Units.UnitIndex" & vbCrLf & "GROUP BY UPI_Events.StartTime, UPI_Events.TagIndex, UPI_Events." _
        , "TypeIndex, UPI_Tags.UnitIndex, UPI_Tags.Tag, UPI_Tags.Description, UPI_Units.SectionIndex, UPI_Sections.Section" & vbCrLf & "HAVING (UPI_Events.TypeIndex=1) AND (UPI_Events.StartTime>={ts '2003-02-01 00:00:00'}) " _
        , "OR (UPI_Events.TypeIndex=2) AND (UPI_Events.StartTime>={ts '2003-02-01 00:00:00'})OR (UPI_Events.TypeIndex=3)AND (UPI_Events.StartTime>={ts '2003-02-01 00:00:00'})")

I apologize for the length of this, but if you just look over it you'll perhaps see what I mean.

Excel automatically trimmed formatted the array in the most odd way. I cannot for the life of me figure out why there are commas where they are. For example, the word "FROM" is split up into "F", "ROM. If I attempt to combine the F and the ROM into one word, thus eliminating two double quotes and the comma, the code craps out with a "type mismatch" error. If I leave it the way it is it works just fine.

I'd REALLY like to format the string so that it'll be more easily manaed when I start creating more pivot tables and such.

PLEASE HELP!! THANK YOU!! Onwards,

Q-

 

[Quintios]

Well, as it turns out maybe I won't have to figure this stuff out. I got this code from the MSDN website somewhere. It takes an SQL string and turns it into an array that can be used in a pivot table pivottablewizard VBA code:

Public Sub ArrayTest1()

    Dim cn As Variant
    Dim strSQL As Variant
    
    strSQL = "SELECT Count(UPI_Events.Index) AS CountOfIndex" & vbCrLf
    strSQL = strSQL & "        FROM UPI_Events;"
    
    cn = Array(Array("ODBC;DSN=MS Access 97 Database;DBQ=c:\UPI\UPI_DATA_UNIT2.MDB;DefaultDir=c:\UPI;DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeo"), Array("ut=5;"))

'Array(StringToArray(strSQL)), _

    ActiveSheet.PivotTableWizard _
        SourceType:=xlExternal, _
        SourceData:=Array(strSQL), _
        TableDestination:="R1C1", _
        TableName:="PivotTable2", _
        BackgroundQuery:=False, _
        Connection:=cn
    
    ActiveSheet.PivotTables("PivotTable2").PivotFields("CountOfIndex").Orientation = xlDataField



End Sub

Function StringToArray(strSQL As Variant) As Variant

      Const StrLen = 127 ' Set the maximum string length for
                         ' each element in the array to return
                         ' to 127 characters.
      Dim NumElems As Integer
      Dim Temp() As String
      Dim i As Integer

'Divide the length of the string Query by StrLen and add 1 to determine
'how many elements the String array Temp should contain, and redimension
'the Temp array to contain this number of elements.

      NumElems = (Len(strSQL) / StrLen) + 1
      ReDim Temp(1 To NumElems) As String

'Build the Temp array by sequentially extracting 127 segments of the Query
'string into each element of the Temp array.

      For i = 1 To NumElems
         Temp(i) = Mid(strSQL, ((i - 1) * StrLen) + 1, StrLen)
      Next i

      ' Set the function StringToArray to the Temp array so it
      ' can be returned to the calling procedure.

      StringToArray = Temp

   End Function

I hope this is useful for someone else. Man, if I hadn't found that code I don't know what I would have done. I have not tested this on longer strings nor more complicated SQL statements, but it works for right now... Onwards,

Q-