none
SQL Pivot through VBA for Excel RRS feed

  • Question

  • I have a sheet [Data$]
    with columns like: [mbr_pgm_id], [Quarteryear]

    I have rows for 4 quarters.

    I am running sql in vba to get some numbers from this sheet.
    And I would like to get:
    2015Q4 2016Q1 2016Q2 2016Q3
     2131     1213     123        100

    I currently have this, but it would give me 4 rows

    strSQL = "Select [Quarteryear], count(*) as CasesCount " & _
             "from [Data$] where [Program_Status] = 'Referral' and [begin_status]='Y'" & _
             " and [PROGRAM] = '" + program + "' GROUP BY [Quarteryear]"

    Set dbConnection = New ADODB.Connection
    With dbConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0;"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
        .Open
    End With
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = dbConnection
        .CommandText = strSQL
    End With
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = dbConnection
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open cmd
    End With

    How can I modify my strSQL?

    I am trying with

    strSQL = "Select '2015Q4','2016Q1','2016Q2','2016Q3' " & _
             "from [Data$] Pivot ( sum([MBR_PGM_ID]) FOR [Quarteryear] IN ([2015Q4],[2016Q1],[2016Q2],[2016Q3])) AS PIVOTTABLE"

    But it vba seems not to support it.

    Any help would be appreciate, thanks

     

    Thursday, November 10, 2016 5:58 PM

Answers

  • Hi msofteagle,

    Based on your code, it seems you want to get the result from current workbook. If so, I would suggest you try to involve PivotTables which is powerful in data area instead of ADODB.

    Here is a recorded VBA code for PivotTables.

    Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Sheet2!R1C4:R9C5", Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _
            :=xlPivotTableVersion15
        Sheets("Sheet4").Select
        Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable3").PivotFields("Quarteryear")
            .Orientation = xlColumnField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
            "PivotTable3").PivotFields("mbr_pgm_id"), "Sum of mbr_pgm_id", xlSum

    If you insist on SQL query, I suggest you try below:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    strFile = "D:\OfficeDev\Excel\201611\Test.xlsx" 'Workbooks(1).FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Open strCon
    ''Pick one:
    'strSQL = "SELECT * FROM DataTable" ''Named range
    'strSQL = "SELECT * FROM [Sheet2$D1:F9]" ''Range
    strSQL = "TRANSFORM Sum([Sheet2$D1:F9].[mbr_pgm_id]) AS SumOfmbr_pgm_id SELECT [Sheet2$D1:F9].[QT] FROM [Sheet2$D1:F9] GROUP BY [Sheet2$D1:F9].[QT] PIVOT [Sheet2$D1:F9].[Quarteryear];"
    rs.Open strSQL, cn
    Debug.Print rs.GetString

    If you want to get the first row 2015Q4, you need to loop through the fields in rs.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by msofteagle Friday, November 11, 2016 4:50 PM
    Friday, November 11, 2016 6:51 AM

All replies

  • Hi msofteagle,

    Based on your code, it seems you want to get the result from current workbook. If so, I would suggest you try to involve PivotTables which is powerful in data area instead of ADODB.

    Here is a recorded VBA code for PivotTables.

    Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Sheet2!R1C4:R9C5", Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _
            :=xlPivotTableVersion15
        Sheets("Sheet4").Select
        Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable3").PivotFields("Quarteryear")
            .Orientation = xlColumnField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
            "PivotTable3").PivotFields("mbr_pgm_id"), "Sum of mbr_pgm_id", xlSum

    If you insist on SQL query, I suggest you try below:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    strFile = "D:\OfficeDev\Excel\201611\Test.xlsx" 'Workbooks(1).FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    cn.Open strCon
    ''Pick one:
    'strSQL = "SELECT * FROM DataTable" ''Named range
    'strSQL = "SELECT * FROM [Sheet2$D1:F9]" ''Range
    strSQL = "TRANSFORM Sum([Sheet2$D1:F9].[mbr_pgm_id]) AS SumOfmbr_pgm_id SELECT [Sheet2$D1:F9].[QT] FROM [Sheet2$D1:F9] GROUP BY [Sheet2$D1:F9].[QT] PIVOT [Sheet2$D1:F9].[Quarteryear];"
    rs.Open strSQL, cn
    Debug.Print rs.GetString

    If you want to get the first row 2015Q4, you need to loop through the fields in rs.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by msofteagle Friday, November 11, 2016 4:50 PM
    Friday, November 11, 2016 6:51 AM
  • Thanks for your answer.
    Friday, November 11, 2016 4:50 PM