Pivot-Tabellen und -Charts automatisch erstellen

Da wir in Access seit Version 2013 keine eingebaute Anzeige von Pivot-Tabellen oder -Charts haben, müssen wir uns mit Excel behelfen. Die Grundlagen dazu finden Sie im Beitrag “Pivot-Tabellen und -Diagramme in Excel”. Nun schauen wir uns an, wie wir von Access aus per Schaltfläche die aktuellen Daten einer dafür vorgesehenen Abfrage nach Excel exportieren und dieser Excel-Datei eine Pivot-Tabelle hinzufügen können.

Ziel dieses Beitrags

Im Beitrag Pivot-Tabellen und -Diagramme in Excel (www.access-im-unternehmen.de/1286) haben wir uns angesehen, wie Sie die Daten einer Access-Abfrage manuell in eine Excel-Datei exportieren und dieser dann in einem neuen Arbeitsblatt eine Pivot-Tabelle hinzufügen. Diesen Vorgang wollen wir nun automatisieren. Das heißt, dass der Benutzer nur eine Schaltfläche anklicken soll und dann alle anderen Schritte automatisch ablaufen – bis zur Anzeige der Seite der Excel-Datei mit der Pivot-Tabelle. Das ist eine Möglichkeit, Kunden die gewünschte Auswertung anzeigen zu lassen. Weitergehende Anpassungen kann der Kunde dann in der Excel-Datei selbst vornehmen.

Excel automatisieren

Die Automatisierung von Excel ist wesentlich einfacher, wenn wir einen Verweis zur Objektbibliothek von Excel herstellen. Wir können dann zum Beispiel im VBA-Editor IntelliSense nutzen. Den Verweis fügen Sie hinzu, indem Sie im VBA-Editor (Alt + F11) den Menüpunkt Extras|Verweise betätigen und dort den Eintrag Microsoft Excel 16.0 Object Library hinzufügen (siehe Bild 1).

Verweis auf die Excel-Objektbibliothek

Bild 1: Verweis auf die Excel-Objektbibliothek

Daten exportieren

Das Exportieren der Daten für die gewünschte Tabelle oder Abfrage erledigen wir mit der Funktion DatenExportieren (siehe Listing 1). Diese erwartet zwei Parameter:

Private Sub DatenExportieren(strQuelle As String, strDatei As String)
     On Error Resume Next
     Kill strDatei
     On Error GoTo 0
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQuelle, strDatei, True
End Sub

Listing 1: Exportieren der Datenquelle in eine Excel-Datei

  • strQuelle: Name der zu exportierenden Tabelle oder Abfrage
  • strDatei: Pfad zu der zu erstellenden Datei

Die Funktion ruft bei deaktivierter Fehlerbehandlung die Anweisung Kill auf. Diese löscht eine eventuell bereits vorhandene Datei mit dem Namen aus strDatei. Die Fehlerbehandlung wird deaktiviert, weil Kill einen Fehler auslöst, wenn die angegebene Datei nicht vorhanden ist. Danach verwendet die Funktion die Methode DoCmd.TransferSpreadsheet, um die Daten aus der mit strQuelle angegebenen Tabelle oder Abfrage in die Datei aus strDatei zu exportieren.

Als Format verwenden wir acSpreadsheetTypeExcel12Xml. Die Dateiendung muss in diesem Fall .xlsx lauten.

Excel starten

Um die Excel-Instanz zu starten, in der wir die Pivot-Tabelle erzeugen wollen, nutzen wir eine weitere kleine Prozedur. Diese heißt ExcelErzeugen und sieht wie folgt aus:

Private Sub ExcelErzeugen()
     Set objExcel = New Excel.Application
     objExcel.Visible = True
End Sub

Für die hier erzeugte Excel-Instanz fügen wir im Klassenmodul des Formulars, aus dem wir die Funktionen aufrufen wollen, eine passende Objektvariable hinzu:

Dim objExcel As Excel.Application

Um das soeben erstellte Workbook zu öffnen, verwenden wir diese Funktion:

Private Function WorkbookOeffnen(strDatei As String)  As Excel.Workbook
     Set WorkbookOeffnen = objExcel.Workbooks.Open(strDatei)
End Function

Dann kommen wir endlich zur Prozedur, die wir durch das Ereignis Beim Klicken einer Schaltfläche namens cmdPivotTabelleInExcelErstellen auslösen (siehe Listing 2).

Private Sub cmdPivotTabelleInExcelErstellen_Click()
     Dim objWorkbook As Excel.Workbook
     Dim strDatei As String
     Dim strQuelle As String
     strQuelle = "qryPivot"
     strDatei = CurrentProject.Path & "\qryPivot.xlsx"
     DatenExportieren strQuelle, strDatei
     ExcelErzeugen
     Set objWorkbook = WorkbookOeffnen(strDatei)
     '... weitere Anweisungen
End Sub

Listing 2: Exportieren und Öffnen der Daten

Diese deklariert eine Variable für das WorkBook-Objekt sowie für den Namen der Quelle und der zu erstellenden Datei und füllt diese noch mit Werten.

Diese Werte können Sie auch über entsprechende Steuerelemente im Formular durch den Benutzer füllen lassen.

Solange wir eine Pivot-Tabelle für eine spezielle Datenquelle erstellen, macht es aber keinen Sinn, eine andere als die dafür vorgesehene Datenquelle anzugeben.

Danach ruft die Prozedur nacheinander die bereits vorgestellten Routinen DatenExportieren, ExcelErzeugen und objWorkbook auf. Das Ergebnis ist eine geöffnete Excel-Instanz, welche die exportierten Daten in einem Work-sheet anzeigt (siehe Bild 2).

Die exportierten Excel-Daten

Bild 2: Die exportierten Excel-Daten

Nun benötigen wir noch ein zweites Worksheet, in dem wir die Pivot-Tabelle anlegen.

Pivot-Tabelle erzeugen

Das Erzeugen der eigentlichen Pivot-Tabelle lagern wir in die Prozedur PivotTabelleErzeugen aus, die zwei Parameter entgegennimmt:

  • objWorkbook: Verweis auf die Excel-Datei
  • strQuelle: Name der Quelltabelle oder -abfrage

Die Prozedur aus Listing 3 deklariert einige Variablen, zum Beispiel für das Worksheet mit den Daten und mit der zu erzeugenden Pivot-Tabelle oder für die verschiedenen PivotField-Elemente. Sie referenziert zuerst das beim Exportieren automatisch erstellte Worksheet mit dem Namen aus strQuelle, in unserem Beispiel qryPivot.

Private Sub PivotTabelleErzeugen(objWorkbook As Workbook, strQuelle As String)
     Dim objDataSheet As Worksheet
     Dim objPivotSheet As Worksheet
     Dim objRange As Range
     Dim objPivotCache As PivotCache
     Dim objPivotTabelle As PivotTable
     Dim objPivotZeile1 As PivotField
     Dim objPivotZeile2 As PivotField
     Dim objPivotSpalte As PivotField
     Dim objPivotWerte As PivotField
     Dim lngLetzteSpalte As Long
     Set objDataSheet = objWorkbook.Sheets(strQuelle)
     Set objPivotSheet = objWorkbook.Sheets.Add(objDataSheet)
     objDataSheet.Name = "Pivot-Daten"
     objPivotSheet.Name = "Pivot-Tabelle"
     Set objRange = BereichErmitteln(objDataSheet)
     Set objPivotCache = objWorkbook.PivotCaches.Create(xlDatabase, objRange)
     Set objPivotTabelle = objPivotCache.CreatePivotTable(objPivotSheet.Cells(1, 1), "Bestellungen")
     Set objPivotZeile1 = objPivotTabelle.PivotFields("Kategoriename")
     With objPivotZeile1
         .Orientation = xlRowField
     End With
     Set objPivotZeile2 = objPivotTabelle.PivotFields("Artikelname")
     With objPivotZeile2
         .Orientation = xlRowField
     End With
     Set objPivotSpalte = objPivotTabelle.PivotFields("Land")
     With objPivotSpalte
         .Orientation = xlColumnField
     End With
     Set objPivotWerte = objPivotTabelle.PivotFields("Anzahl")
     With objPivotWerte
         .Orientation = xlDataField
     End With
     With objPivotTabelle
         .ShowTableStyleRowStripes = True
         .TableStyle2 = "PivotStyleMedium9"
     End With
     lngLetzteSpalte = objPivotSheet.Cells(2, objPivotSheet.Columns.Count).End(xlToLeft).Column
     Set objRange = objPivotSheet.Range(objPivotSheet.Cells(2, 2), objPivotSheet.Cells(2, lngLetzteSpalte))
     With objRange
         .Orientation = 90
         .EntireColumn.AutoFit
     End With
End Sub

Listing 3: Erzeugen der Pivot-Tabelle

Dann fügt sie ein neues Worksheet hinzu, und zwar vor dem bereits bestehenden Worksheet mit den Rohdaten. Das erreichen wir, indem wir der Add-Methode als ersten Parameter das Worksheet angeben, vor dem das neue Worksheet erscheinen soll. Danach stellt die Prozedur die Namen der beiden Worksheets auf Pivot-Tabelle und Pivot-Daten ein.

Mit der Hilfsfunktion BereichErmitteln liest die Prozedur den Bereich mit den Daten aus dem mit objDataSheet referenzierten Worksheet ein:

Private Function BereichErmitteln( objDataSheet As Worksheet) As Excel.Range
     Dim lngLetzteZeile As Long
     Dim lngLetzteSpalte As Long
     lngLetzteZeile = objDataSheet.Cells( objDataSheet.Rows.Count, 1).End(xlUp).Row
     lngLetzteSpalte = objDataSheet.Cells(1,  objDataSheet.Columns.Count).End(xlToLeft).Column
     Set BereichErmitteln = objDataSheet.Cells(1,  1).Resize(lngLetzteZeile, lngLetzteSpalte)
End Function

Danach wird ein beim manuellen Erstellen nicht in Erscheinung tretender Cache auf Basis der zu berücksichtigenden Daten erzeugt.

Dieser dient als Grundlage für den Aufruf der Methode CreatePivotTable, der wir die als linke, obere Zelle zu verwendende Zelle des Ziel-Worksheets sowie eine Bezeichnung als Parameter übergeben.

Danach legen wir fest, welche Daten als Zeilenköpfe, Spaltenköpfe und als Werte genutzt werden sollen. Dabei weisen wir den Variablen objPivotZeile1 und objPivotZeile2 die Felder Kategoriename und Artikelname zu und legen für diese jeweils den Wert xlRowField als Wert der Eigenschaft Orientation fest.

Für die Variable objPivotSpalte nutzen wir das Feld Land sowie den Wert xlColumnField für die Eigenschaft Orientation.

Schließlich fehlen noch die Werte, die wir mit objPivotWerte referenzieren und die aus dem Feld Anzahl stammen. Hier lautet der Wert für die Eigenschaft Orientation auf xlDataField.

Danach brauchen wir die Pivot-Tabelle nur noch zu formatieren. Das erledigen wir durch Verwendung des Wertes True für die Eigenschaft ShowTableStyleRowStripes, damit die einzelnen Zeilen durch eine Linie voneinander getrennt werden sowie der Vorlage PivotStyleMedium9 für die Eigenschaft TableStyle2.

Nun wollen wir noch die Spaltenüberschriften vertikal darstellen, damit wir die Spalten schmaler gestalten können. Dazu ermitteln wir den Index der letzten Spalte und speichern diesen in lngLetzteSpalte. Dann legen wir einen Bereich fest, der von der Zelle in der zweiten Spalte und der zweiten Zeile bis zur Zelle in der letzten Spalte und der zweiten Zeile reicht. Für diesen Bereich stellen wir die Eigenschaft Orientation auf 90 ein, damit die Spaltenüberschriften vertikal erscheinen. Außerdem passen wir die Spaltenbreite an die optimale Breite an, indem wir die AutoFit-Methode der Eigenschaft EntireColumn des Bereichs aufrufen.

Das Ergebnis sehen Sie in Bild 3. Mit dem Code erstellen Sie in wenigen Sekunden eine Pivot-Tabelle unter Excel, die der Benutzer nach eigenen Wünschen durch Sortieren oder Filtern der Zeilen und Spalten anpassen kann.

Ergebnis der Prozedur

Bild 3: Ergebnis der Prozedur

Zusammenfassung und Ausblick

Dieser Beitrag zeigt, wie Sie automatisiert eine Pivot-Tabelle auf der Basis von Access-Daten erstellen können. Dabei haben wir eine vorgegebene Pivot-Tabelle erzeugt.

Sie können natürlich noch mehr Flexibilität erhalten, wenn Sie etwa die Auswahl der in der Pivot-Tabelle in den Zeilen- oder Spaltenüberschriften anzuzeigenden Daten beziehungsweise die anzuzeigenden Werte ermöglichen.

Downloads zu diesem Beitrag

Enthaltene Beispieldateien:

PivotAutomatischErstellen.accdb

Download

Schreibe einen Kommentar