dinsdag 14 april 2015

Excel: Microsoft Query and Slicers (2013)

For some reason I was under the impression that Microsoft Query was outdated and should me replaced by PowerPivot. While working on some VBA scripts to create a ListObject with a QueryTable (what you need to use a Microsoft Query), I all of a sudden discovered a new feature for Microsoft Query: Slicers! Or better said, the button Insert Slicer.


And of course, the slicer you create is automatically connected to your dataset:


Got a remark from Craig Hatmaker, in version 2013, slicers work with all tables. So, Microsft Query tables, Pivot Tables and regular lists,changed into a table. And of course, connected charts are adjusted with your choice.

By the way, the VBA scripts I was working on:

Sub ListObjectAdd()
    'create listobject connected to database
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=ndw;", Destination:=Range("$A$1")).QueryTable
        .CommandText = "SELECT * FROM Customers"
        .ListObject.DisplayName = "Tabel_Query_van_ndw"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub ListObjectSQL()
    'change SQL commandtext
    Dim strConnection As String
    Dim strSQL As String
    
    strSQL = "SELECT country, ContactName, CompanyName FROM Customers"
    strConnection = "ODBC;DSN=ndw;"

    'ActiveSheet.ListObjects("Tabel_Query_van_ndw").QueryTable.Connection = strConnection
    ActiveSheet.ListObjects("Tabel_Query_van_ndw").QueryTable.CommandText = strSQL
    ActiveSheet.ListObjects("Tabel_Query_van_ndw").QueryTable.Refresh
End Sub

Sub ListObjectDelete()
    'delete listobject
    ActiveSheet.ListObjects("Tabel_Query_van_ndw").Delete
End Sub

Sub SlicerAdd()
    'add slicer
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Tabel_Query_van_ndw") _
        , "country").Slicers.Add ActiveSheet, , "country", "country", 189, 639.75, 144 _
        , 198.75
End Sub
Een reactie posten