Dieser Beitrag zeigt, wie man MS Access Daten dynamisch in
lokalen MS Excel Dateien auswerten kann
Datenauswertungen in MS Access können seit einiger Zeit nicht
mehr über ein integriertes Pivot, PivotTable, PivotChart ausgewertet werden.
Die Auswertung über ActiveX-Charts ist ebenfalls nicht mehr
gewartet.
Deshalb ist es sinnvoll Daten über die mächtigen Excel
Funktionen auszuführen. Dabei muss in Excel eine OLEDB Datenverbindung von
Excel zur lokalen Access Datenbank einmalig eingerichtet werden.
Excel greift bei der externen Abfrage auf Access Tabellen
und Abfragen
Damit die Daten definiert übergeben warden empfiehlt es
sich, in Access die Daten als Backend vorzubereiten und mit einer Kennung zu
deklarieren.
In diesem Beispiel dienen alle Access Queries mit der
Bezeichung xls_* als Datenschnittstelle zu Excel.
Die Daten erscheinen local und extern wie erweiterte Abfrage-Tabellen.
Diese können jedoch aus mehreren Teiltabellen zusammengefügt werden.
Unter Excel können dann die gleichen Daten als PivotChart
oder als PivotTable ausgewertet werden.
Dabei bleibt das PivotChart so aktiv, dass in der PivotChart
oder der angebundenen PivotTable die Felder per Dropdown gefiltert werden
können oder von Spalten zu Zeilen gezogen werden können.
In der PivotTabelle und PivotChart werden dabei automatisch
die Summen über Felder gezogen. Wahlweise kann das Ergebnis in Anzahl oder
anderen Funktionen zusammengefasst werden.
Als Basis dient die einfache tabellarische Daten-Darstellung
der angebundenen Daten.
Erste Auswertung anbinden
In Microsoft Excel muss zunächst über Menü Ribbonbar->Daten->Daten
abrufen->Aus Datenbank->Aus Microsoft Access-Datenbank die Datenbank
ausgewählt werden
Dann muss man die Access-Datei beim ersten Aufsetzen manuell
einstellen
Danach kann man über den Daten Navigator die xls_ Abfragen
auswählen
Die Datenverbindungen sind als OleDb Verbindungen in der
Excel-Datei selbst gespeichert.
Man findet die Datenverbindungen über
Menü->Daten->Abfragen und Verbindungen
Im Bereich Abfragen und Verbindungen muss man auf Abfragen
wechseln
Den Inhalt der Abfrage kann man mit Bearbeiten im Kontext
der Abfrage öffnen
Der Eintrag findet man dann im Abfrage-Editor unter
Angewandte Schritte rechts unten indem man auf Quelle ändert
Und dann das Settings-Icon bei Quelle wählt.
Hier kann man manuell den Datenpfad ändern.
Datenverbindung
per vba ändern
Datenverbindung in
der lokalen Excel Auswertung einstellen
Wenn sich der Pfad zur Datenbank verändert hat, weil man die
Datenbank verteilt hat oder den Pfad verändert hat, dann muss der integrierte
Pfad in Excel geändert werden.
Damit die Ausgabe-Excel-Datei sich auch auf die lokale
Datenbank bezieht, kann man den Pfad per vba Code in Ms Access einstellen.
Hierzu kann man das beiliegende Formular verwenden. Die OLEDB Verbindung muss nur einmalig
eingestellt werden, entweder manuell oder per Automatik.
Vba Code zum Anbinden
Vba Code zum Tauschen der oledb Verbindung
|
Public Sub connect_Excel(ByVal sLocal_Excel_Filename As String)
'----------------<
open_Excel() >----------------
If sLocal_Excel_Filename Like "" Then
MsgBox "Excel
File as Parameter missing", vbCritical
Exit Sub
End If
'< init
Path >
Dim sFilename_with_Path As String
sFilename_with_Path = CurrentProject.Path & "\" & sLocal_Excel_Filename
Dim sDB_with_Path As String
sDB_with_Path = CurrentProject.FullName
'</
init Path >
'< open
Excel >
Dim workbook As Excel.workbook
Set workbook =
Excel.Workbooks.Open(sFilename_with_Path)
'</
open Excel >
'-------------------<
Change Connection to new AccessDB >-------------
Dim query As WorkbookQuery
'----<
@Loop: Queries >----
For Each query In workbook.Queries
'----<
Query >----
DoCmd.Hourglass True
'<
analyse string >
Dim sFormula As String
sFormula = query.Formula
Dim posStart As Integer
posStart = InStr(1, sFormula, "Contents(""", vbTextCompare)
Dim posEnd As Integer
posEnd = InStr(1, sFormula, ".accdb", vbTextCompare)
Dim sLeft As String
sLeft = Mid(sFormula, 1, posStart + 9)
Dim sEnd As String
sEnd = Mid(sFormula, posEnd + 6)
'</
analyse string >
'<
replace >
sFormula = sLeft & sDB_with_Path & sEnd
'</
replace >
'< set
>
'*to local
DB
query.Formula = sFormula
'</ set
>
DoCmd.Hourglass False
'----</ Query >----
Next
'----<
@Loop: Queries >----
'-------------------</
Change Connection to new AccessDB >-------------
workbook.Close True
MsgBox "Done"
'----------------</
open_Excel() >----------------
End Sub
|
Vba Code
Kompletter vba Code zu Anbinden und Öffnen von Excel Dateien
|
Option Compare Database
Option Explicit On
Private Sub btnExcel_Click()
connect_Excel tbxFile.Value
End Sub
Private Sub btnOpen_Excel_Click()
Shell "Excel
"""
& CurrentProject.Path & "\" & tbxExcel_File.Value & """", vbMaximizedFocus
' Dim objExcel As Excel.Application
' Set objExcel = New Excel.Application
' objExcel.Visible = True
' objExcel.Workbooks.Open
CurrentProject.Path & "\" & tbxExcel_File.Value, , , , , ,
, , , , , , True
' 'close ueber Datei
End Sub
Public Sub connect_Excel(ByVal sLocal_Excel_Filename As String)
'----------------<
open_Excel() >----------------
If sLocal_Excel_Filename Like "" Then
MsgBox "Excel
File as Parameter missing", vbCritical
Exit Sub
End If
'< init
Path >
Dim sFilename_with_Path As String
sFilename_with_Path = CurrentProject.Path & "\" & sLocal_Excel_Filename
Dim sDB_with_Path As String
sDB_with_Path = CurrentProject.FullName
'</
init Path >
'< open
Excel >
Dim workbook As Excel.workbook
Set workbook =
Excel.Workbooks.Open(sFilename_with_Path)
'</
open Excel >
'-------------------<
Change Connection to new AccessDB >-------------
Dim query As WorkbookQuery
'----<
@Loop: Queries >----
For Each query In workbook.Queries
'----<
Query >----
DoCmd.Hourglass True
'<
analyse string >
Dim sFormula As String
sFormula = query.Formula
Dim posStart As Integer
posStart = InStr(1, sFormula, "Contents(""", vbTextCompare)
Dim posEnd As Integer
posEnd = InStr(1, sFormula, ".accdb", vbTextCompare)
Dim sLeft As String
sLeft = Mid(sFormula, 1, posStart + 9)
Dim sEnd As String
sEnd = Mid(sFormula, posEnd + 6)
'</
analyse string >
'<
replace >
sFormula = sLeft & sDB_with_Path & sEnd
'</
replace >
'< set
>
'*to local
DB
query.Formula = sFormula
'</ set
>
DoCmd.Hourglass False
'----</ Query >----
Next
'----<
@Loop: Queries >----
'-------------------</
Change Connection to new AccessDB >-------------
workbook.Close True
MsgBox "Done"
'----------------</
open_Excel() >----------------
End Sub
'let
'Quelle =
Access.Database(File.Contents("C:\_Daten\Desktop\Demo\Access\2017-11-15
Access_Excel\20171115_Access_Excel.accdb"),
[CreateNavigationProperties=true]),
'_xls_Cars =
Quelle{[Schema="",Item="xls_Cars"]}[Data]
'in
'_xls_Cars"
'
'qry.Formula = sFormula
'"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source='" & sDB_with_Path & "'"
'"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data
Source=$Workbook$;Location=xls_Cars;Extended Properties="""
|
Video Tutorial