Pivot-Tabellen und -Charts automatisch erstellen

1/2021, 2021, Interaktiv

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

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

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar