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

Sie haben das Ende des frei verfügbaren Textes erreicht. Möchten Sie ...

TestzugangOder haben Sie bereits Zugangsdaten? Dann loggen Sie sich gleich hier ein:

Schreibe einen Kommentar