#

Download:

Datei 1: Access_to_Excel.accdb
Datei 2: Excel_with_Access.xlsx

 

 

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

Mobile

.

0123movie.net