<\/p>\n
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.<\/b><\/p>\nZiel dieses Beitrags<\/h2>\n
Im Beitrag Pivot-Tabellen und -Diagramme in Excel <\/b>(www.access-im-unternehmen.de\/1286<\/b>) 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.<\/p>\nExcel automatisieren<\/h2>\n
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<\/b>) den Menüpunkt Extras|Verweise <\/b>betätigen und dort den Eintrag Microsoft Excel 16.0 Object Library <\/b>hinzufügen (siehe Bild 1).<\/p>\n
![\"Verweis](\"..\/fileadmin\/_temp_\/2021_01\/pic_1287_001.png\")
<\/p>\n
Bild 1: Verweis auf die Excel-Objektbibliothek<\/span><\/b><\/p>\n Das Exportieren der Daten für die gewünschte Tabelle oder Abfrage erledigen wir mit der Funktion DatenExportieren <\/b>(siehe Listing 1). Diese erwartet zwei Parameter:<\/p>\n Listing 1: Exportieren der Datenquelle in eine Excel-Datei<\/span><\/b><\/p>\n Die Funktion ruft bei deaktivierter Fehlerbehandlung die Anweisung Kill <\/b>auf. Diese löscht eine eventuell bereits vorhandene Datei mit dem Namen aus strDatei<\/b>. Die Fehlerbehandlung wird deaktiviert, weil Kill <\/b>einen Fehler auslöst, wenn die angegebene Datei nicht vorhanden ist. Danach verwendet die Funktion die Methode DoCmd.TransferSpreadsheet<\/b>, um die Daten aus der mit strQuelle <\/b>angegebenen Tabelle oder Abfrage in die Datei aus strDatei <\/b>zu exportieren.<\/p>\n Als Format verwenden wir acSpreadsheetTypeExcel12Xml<\/b>. Die Dateiendung muss in diesem Fall .xlsx <\/b>lauten.<\/p>\n Um die Excel-Instanz zu starten, in der wir die Pivot-Tabelle erzeugen wollen, nutzen wir eine weitere kleine Prozedur. Diese heißt ExcelErzeugen <\/b>und sieht wie folgt aus:<\/p>\n 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:<\/p>\n Um das soeben erstellte Workbook zu öffnen, verwenden wir diese Funktion:<\/p>\n Dann kommen wir endlich zur Prozedur, die wir durch das Ereignis Beim Klicken <\/b>einer Schaltfläche namens cmdPivotTabelleInExcelErstellen <\/b>auslösen (siehe Listing 2).<\/p>\n <\/p>\n Listing 2: Exportieren und Öffnen der Daten<\/span><\/b><\/p>\n Diese deklariert eine Variable für das WorkBook<\/b>-Objekt sowie für den Namen der Quelle und der zu erstellenden Datei und füllt diese noch mit Werten.<\/p>\n Diese Werte können Sie auch über entsprechende Steuerelemente im Formular durch den Benutzer füllen lassen.<\/p>\n 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.<\/p>\n Danach ruft die Prozedur nacheinander die bereits vorgestellten Routinen DatenExportieren<\/b>, ExcelErzeugen <\/b>und objWorkbook <\/b>auf. Das Ergebnis ist eine geöffnete Excel-Instanz, welche die exportierten Daten in einem Work-sheet anzeigt (siehe Bild 2).<\/p>\n Bild 2: Die exportierten Excel-Daten<\/span><\/b><\/p>\n Nun benötigen wir noch ein zweites Worksheet, in dem wir die Pivot-Tabelle anlegen.<\/p>\n Das Erzeugen der eigentlichen Pivot-Tabelle lagern wir in die Prozedur PivotTabelleErzeugen <\/b>aus, die zwei Parameter entgegennimmt:<\/p>\n 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<\/b>-Elemente. Sie referenziert zuerst das beim Exportieren automatisch erstellte Worksheet mit dem Namen aus strQuelle<\/b>, in unserem Beispiel qryPivot<\/b>.<\/p>\n Listing 3: Erzeugen der Pivot-Tabelle<\/span><\/b><\/p>\n 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<\/b>-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 <\/b>und Pivot-Daten <\/b>ein.<\/p>\n Mit der Hilfsfunktion BereichErmitteln <\/b>liest die Prozedur den Bereich mit den Daten aus dem mit objDataSheet <\/b>referenzierten Worksheet ein:<\/p>\n Danach wird ein beim manuellen Erstellen nicht in Erscheinung tretender Cache auf Basis der zu berücksichtigenden Daten erzeugt.<\/p>\n Dieser dient als Grundlage für den Aufruf der Methode CreatePivotTable<\/b>, der wir die als linke, obere Zelle zu verwendende Zelle des Ziel-Worksheets sowie eine Bezeichnung als Parameter übergeben.<\/p>\n Danach legen wir fest, welche Daten als Zeilenköpfe, Spaltenköpfe und als Werte genutzt werden sollen. Dabei weisen wir den Variablen objPivotZeile1 <\/b>und objPivotZeile2 <\/b>die Felder Kategoriename <\/b>und Artikelname <\/b>zu und legen für diese jeweils den Wert xlRowField <\/b>als Wert der Eigenschaft Orientation <\/b>fest.<\/p>\n Für die Variable objPivotSpalte <\/b>nutzen wir das Feld Land <\/b>sowie den Wert xlColumnField <\/b>für die Eigenschaft Orientation<\/b>.<\/p>\n Schließlich fehlen noch die Werte, die wir mit objPivotWerte <\/b>referenzieren und die aus dem Feld Anzahl <\/b>stammen. Hier lautet der Wert für die Eigenschaft Orientation <\/b>auf xlDataField<\/b>.<\/p>\n Danach brauchen wir die Pivot-Tabelle nur noch zu formatieren. Das erledigen wir durch Verwendung des Wertes True für die Eigenschaft ShowTableStyleRowStripes<\/b>, damit die einzelnen Zeilen durch eine Linie voneinander getrennt werden sowie der Vorlage PivotStyleMedium9 <\/b>für die Eigenschaft TableStyle2<\/b>.<\/p>\n 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<\/b>. 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 <\/b>auf 90 <\/b>ein, damit die Spaltenüberschriften vertikal erscheinen. Außerdem passen wir die Spaltenbreite an die optimale Breite an, indem wir die AutoFit<\/b>-Methode der Eigenschaft EntireColumn <\/b>des Bereichs aufrufen.<\/p>\n 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.<\/p>\nDaten exportieren<\/h2>\n
Private Sub <\/span>DatenExportieren(strQuelle As String<\/span>, strDatei As String<\/span>)\r\n On Error Resume Next<\/span>\r\n Kill strDatei\r\n On Error GoTo<\/span> 0\r\n DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQuelle, strDatei, True<\/span>\r\nEnd Sub<\/span><\/pre>\n\n
Excel starten<\/h2>\n
Private Sub <\/span>ExcelErzeugen()\r\n Set<\/span> objExcel = New<\/span> Excel.Application\r\n objExcel.Visible = True<\/span>\r\nEnd Sub<\/span><\/pre>\nDim <\/span>objExcel As <\/span>Excel.Application<\/pre>\nPrivate Function <\/span>WorkbookOeffnen(strDatei As String<\/span>) As <\/span>Excel.Workbook\r\n Set<\/span> WorkbookOeffnen = objExcel.Workbooks.Open(strDatei)\r\nEnd Function<\/span><\/pre>\nPrivate Sub <\/span>cmdPivotTabelleInExcelErstellen_Click()\r\n Dim <\/span>objWorkbook As <\/span>Excel.Workbook\r\n Dim <\/span>strDatei As String<\/span>\r\n Dim <\/span>strQuelle As String<\/span>\r\n strQuelle = \"qryPivot\"\r\n strDatei = CurrentProject.Path & \"qryPivot.xlsx\"\r\n DatenExportieren strQuelle, strDatei\r\n ExcelErzeugen\r\n Set<\/span> objWorkbook = WorkbookOeffnen(strDatei)\r\n '... weitere Anweisungen\r\nEnd Sub<\/span><\/pre>\n![\"Die](\"..\/fileadmin\/_temp_\/2021_01\/pic_1287_002.png\")
<\/p>\nPivot-Tabelle erzeugen<\/h2>\n
\n
Private Sub <\/span>PivotTabelleErzeugen(objWorkbook As <\/span>Workbook, strQuelle As String<\/span>)\r\n Dim <\/span>objDataSheet As <\/span>Worksheet\r\n Dim <\/span>objPivotSheet As <\/span>Worksheet\r\n Dim <\/span>objRange As <\/span>Range\r\n Dim <\/span>objPivotCache As <\/span>PivotCache\r\n Dim <\/span>objPivotTabelle As <\/span>PivotTable\r\n Dim <\/span>objPivotZeile1 As <\/span>PivotField\r\n Dim <\/span>objPivotZeile2 As <\/span>PivotField\r\n Dim <\/span>objPivotSpalte As <\/span>PivotField\r\n Dim <\/span>objPivotWerte As <\/span>PivotField\r\n Dim <\/span>lngLetzteSpalte As Long<\/span>\r\n Set<\/span> objDataSheet = objWorkbook.Sheets(strQuelle)\r\n Set<\/span> objPivotSheet = objWorkbook.Sheets.Add(objDataSheet)\r\n objDataSheet.Name = \"Pivot-Daten\"\r\n objPivotSheet.Name = \"Pivot-Tabelle\"\r\n Set<\/span> objRange = BereichErmitteln(objDataSheet)\r\n Set<\/span> objPivotCache = objWorkbook.PivotCaches.Create(xlDatabase, objRange)\r\n Set<\/span> objPivotTabelle = objPivotCache.CreatePivotTable(objPivotSheet.Cells(1, 1), \"Bestellungen\")\r\n Set<\/span> objPivotZeile1 = objPivotTabelle.PivotFields(\"Kategoriename\")\r\n With<\/span> objPivotZeile1\r\n .Orientation = xlRowField\r\n End With<\/span>\r\n Set<\/span> objPivotZeile2 = objPivotTabelle.PivotFields(\"Artikelname\")\r\n With<\/span> objPivotZeile2\r\n .Orientation = xlRowField\r\n End With<\/span>\r\n Set<\/span> objPivotSpalte = objPivotTabelle.PivotFields(\"Land\")\r\n With<\/span> objPivotSpalte\r\n .Orientation = xlColumnField\r\n End With<\/span>\r\n Set<\/span> objPivotWerte = objPivotTabelle.PivotFields(\"Anzahl\")\r\n With<\/span> objPivotWerte\r\n .Orientation = xlDataField\r\n End With<\/span>\r\n With<\/span> objPivotTabelle\r\n .ShowTableStyleRowStripes = True<\/span>\r\n .TableStyle2 = \"PivotStyleMedium9\"\r\n End With<\/span>\r\n lngLetzteSpalte = objPivotSheet.Cells(2, objPivotSheet.Columns.Count).End(xlToLeft).Column\r\n Set<\/span> objRange = objPivotSheet.Range(objPivotSheet.Cells(2, 2), objPivotSheet.Cells(2, lngLetzteSpalte))\r\n With<\/span> objRange\r\n .Orientation = 90\r\n .EntireColumn.AutoFit\r\n End With<\/span>\r\nEnd Sub<\/span><\/pre>\nPrivate Function <\/span>BereichErmitteln( objDataSheet As <\/span>Worksheet) As <\/span>Excel.Range\r\n Dim <\/span>lngLetzteZeile As Long<\/span>\r\n Dim <\/span>lngLetzteSpalte As Long<\/span>\r\n lngLetzteZeile = objDataSheet.Cells( objDataSheet.Rows.Count, 1).End(xlUp).Row\r\n lngLetzteSpalte = objDataSheet.Cells(1, objDataSheet.Columns.Count).End(xlToLeft).Column\r\n Set<\/span> BereichErmitteln = objDataSheet.Cells(1, 1).Resize(lngLetzteZeile, lngLetzteSpalte)\r\nEnd Function<\/span><\/pre>\n![\"Ergebnis](\"..\/fileadmin\/_temp_\/2021_01\/pic_1287_003.png\")
<\/p>\n