Andreas Unkelbach
Werbung


Sonntag, 11. Mai 2014
16:58 Uhr

Dynamisches Importieren von Daten aus Excel nach Access unter Abfrage Speicherpfad per Makro und VBA

Ausgangslage
Innerhalb einer Access-Datenbank sollen verschiedene Berichte zusammengefasst und durch Abfragen miteinander verbunden werden. Hierzu ist es jedoch erforderlich, dass die zugrunde liegenden Tabellen aktualisiert werden und dafür in die Datenbank importiert werden können. Da es sich um eine Vielzahl von Tabellen handelt ist die Importfunktion in Access etwas umständlich. Vergleichbar zum Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" bietet aber auch Access die Möglichkeit Makros zur Arbeitserleichterung zu verwenden an.
 

Makro unter Access zum Import erstellen

Zur Erstellung eines Makros kann im Ribbon Erstellen in der Befehlsgruppe Andere die Funktion Makro ausgewählt werden.

Hierbei unterscheiden sich die Oberflächen zwischen Access 2007 und Access 2010 erheblich. Daher wird im Folgenden sowohl auf Access 2007 als auch Access 2010 eingegangen.

Makro unter Access 2007 erstellen

Damit alle Funktionen zur Erstellung eines Makros aktiviert sind sollten sowohl die Schaltfläche "Alle Aktionen anzeigen", "Bedingungen" als auch "Argumente" in der Symbolleiste Entwurf aktiviert sein.
Ribbon Entwurf zur Makroerstellungen - Alle Aktionen anzeigen inklusive Bedingungen
Insbesondere die Möglichkeit Bedingungen vor der Ausführung von Aktionen zu hinterlegen ermöglicht es für jede Aktion vorher eine Rückfrage einzuholen.

1. Vorhandene Tabellen löschen
Als ersten Schritt sollen die bestehenden importierten Tabellen in der Datenbank gelöscht werden. Hierzu wird über die Aktion "Löschen Objekt" der Objekttyp Tabelle und über das Feld Objektklasse die eigentliche Tabelle gelöscht. Im Beispiel ist dieses die Tabelle "Grunddaten".
1. Schritt Tabelle löschen über LöschenObjekt
Durch die Bedigung Meldung("Tabelle löschen";1)=1 wird diese Aktion nur bei Bestätigung ausgeführt.

2. Neue/Aktualisierte Tabellen einfügen
Über die Funktion "TransferArbeitsblatt" kann sowohl der Import als auch Export von Tabellen erfolgen. Im Beispiel soll eine Exceltabelle (ExcelWorkbook) als Tabelle "Grundtabelle" importiert werden. Dieses funktioniert über den Transfertyp Importieren, Dateiformat Excel Workbook und Angabe des Tabellennamen "Grundtabelle"
Altion TransferArbeitsblatt unter Access 2007
Hierbei können sowohl der Dateiname hinterlegt werden (im Beispiel B:import.xls) als auch über das Feld Bereich das zu importierende Tabellenblatt (im Beispiel Grunddaten!A-Z) angegeben werden. Hier zeigt sich auch schon der Nachteil von Makros, da sowohl der Dateiname als auch der Speicherort fix angegeben werden müssen.Über die Option Besitzt Feldnamen wird die erste Zeile der Tabelle als Datensatzbeschreibung verwendet. Durch die Bedingung kann wiederum abgesichert werden, dass nicht aus Versehen unbeabsichtigt eine Tabelle importiert wird.

3. Abfrage per Makro starten
Wenn alle Tabellen importiert sind kann über die Aktion "ÖffnenAbfrage" eine entsprechende Abfrage gestartet werden.
Access 2007 Aktion ÖffnenAbfrage
Durch die Ansicht Datenblatt erscheint die Abfrage auch direkt als Auswertung und kann exportiert oder weiter bearbeitet werden.

Makro unter Access 2010 erstellen

Ab Access 2010 hat sich die Darstellung zur Erstellung von Makros von einer Tabellenform und darunter liegenden Attributen zu einer Baumstruktur verändert. Ferner wurde die Aktion TransferArbeitsblatt in die Aktion ImportierenExportierenTabellenblatt umbenannt. Bestehende Makros werden direkt umgewandelt.
Das eingangs beschriebene Makro würde unter Access 2010 wie folgt geschrieben werden:
Darstellung Makro ab Access 2010

Makro zu Visual Basic konvertieren

Sicherlich sind solche Makros eine Arbeitserleichterung beim Import und auch der Verarbeitung von außerhalb der eigentlichen Access Datenbank liegenden Tabellen und sonstigen Daten.

Allerdings sind prinzipbedingt die einzelnen Abfragen auch relativ eingeschränkt in ihrer Funktionsweise. So war es in Access 2007 nur möglich für jede einzelne Aktion eine entsprechende Bedingung zu definieren. Ab Access 2010 können innerhalb der Bedingung auch mehrere Aktionen eingefügt werden. Allerdings ist auch unter Access der Import von Tabellenblättern nur über die Angabe des genauen Dateinamens inklusive Speicherort möglich. Hier gibt es unter VBA aber darüber hinaus gehende Möglichkeiten.  Im ersten Schritt muss hierfür das angelegte Makro in eine VBA Funktion umgewandelt werden

Unter Access 2007 kann über die Datenbanktools die Funktion "Makros zu Visual Basic konvertieren" aufgerufen werden.
Access 2007 Makro zu VBA konvertieren
Ab Access 2010 befindet sich diese Funktion in der Entwurfsansicht des jeweiligen Makros.
Access 2010 Makro zu VBA konvertieren
Unter beiden Funktionen wird das vorher entworfene Makro dann als VBA Code dargestellt und kann als Modul im VBA Editor betrachtet und bearbeitet werden.

Der VBA Code für das oben beschriebene Makro würde dabei wie folgt umgewandelt werden.
Access VBA Code aus Makro
Das beschriebene Makro ist nun als Function angelegt und steht als VBA Modul in der Access Datenbank zur Verfügung.

Per Makro VBA Funktion ausführen

Diese Funktion kann dann als Code über ein neues Makro aufgerufen werden.
Unter Access 2007 ist dieses über die Aktion "AusführenCode" möglich.
Access 2007 AusführenCode
Hier wird direkt die nun vorhandene Funktion Import ohne weitere Argumente ausgeführt.
Ab Access 2010 hat sich hier eigentlich nur die Darstellung verändert, so dass auch hier der Code ausgeführt werden kann.
Access 2010 Funktion ausführen

Erweiterung Makro um VBA Code

Der Vorteil bei der Umwandlung zu VBA besteht darin, dass nun die aus der Makrofunktion bekannten Funktionen erheblich erweitert werden können.

Dynamischer Speicherort zum Import von Daten verwenden

Wie beim Makro TransferArbeitsblatt beziehungsweise ImportierenExportierenTabellenblatt beschrieben muss zum Import einer Tabelle nach Access sowohl der Dateiname der Exceltabelle als auch der Speicherort (im Beispiel war dieses b:import.xls) angegeben werden. Sofern die Datenbank aber flexibel genutzt werden soll, oder aber sich der Speicherort der zu importierenden Daten auch ändern kann, kann folgende Ergänzung des VBA Codes sinnvoll sein.

    Dim Pfad As String
    Pfad = Application.CurrentProject.Path
    
    If MsgBox("Import aus " & Pfad & " ?", vbYesNo) = vbYes Then
       Pfad = InputBox("Pfadnamen ohne abschliessenden ")
    End If
    
    MsgBox "Import aus " & Pfad & " !", vbInformation, "Importquelle"


Über die Anweisung DIM Pfad As String wird eine Variable von Typ String definiert. Diese Variable bekommt als Wert den aktuellen Projektpfad der Accessdatenbank zugewiesen, so dass die Datenbank im Idealfall zu den zu importierenden Dateien kopiert werden kann.
Sofern die Importdaten jedoch in einen anderen Pfad liegen kann über die InputBox ein anderer Pfad angegeben werden. In Form einer Infobox wird danach noch einmal der Importpfad angegeben.

Die Anweisung zum Import der Tabelle import.xls kann nun wie folgt angepasst werden:

If (MsgBox("Tabelle importieren", 1) = 1) Then
        DoCmd.TransferSpreadsheet acImport, 10, "Grundtabelle", Pfad & "import.xls", True, "Grunddaten!A:Z"
End If

Hier wird nun ebenfalls das Tabellenblatt Grunddaten aus der Tabelle import.xls importiert. Jedoch kann der Speicherort vorher nochmals geändert werden. Sofern dieser Pfad nicht geändert wurde, wird der Pfad der Access-Datenbank verwendet.

Verschachtelte Bedingungen

Ein weiterer Vorteil innerhalb VBA ist, dass hier auch mehrere Bedingungen miteinander verschachtelt werden können oder auch mehrere Aktionen nach einer Bedingung gestartet werden können.

Sollen nach erfolgreichen Import der Tabellen auch mehrere Abfragen gestartet werden kann dieses über folgende Anweisung erfolgen.


    MsgBox "Daten wurden importiert", vbOKOnly, "Keine Panik"
    If (MsgBox("Auswertungen starten", 1) = 1) Then
        DoCmd.OpenQuery "110 Auswertung 1", acViewNormal, acReadOnly
        DoCmd.OpenQuery "120 Auswertung 2", acViewNormal, acReadOnly
        DoCmd.OpenQuery "130 Auswertung 3", acViewNormal, acReadOnly
    MsgBox "Speichern Sie die einzelnen Abfragen und kopieren die Daten über Inhalte Einfügen->Transponieren in die Zieltabelle", vbInformation, "Daten ausgewertet"
    End If


Hierdurch werden nach erfolgreichen Importiert der Tabellen drei Auswertungen gestartet und eine Anweisung ausgegeben, wie mit diesen weiter verfahren werden soll. Dieses erfolgt über die Anweisung DoCmd.OpenQuery verbunden mit der Darstellung Normal und Schreibgeschützt. Danach wird über die Anweisung MsgBox eine Anweisung zur weiteren Verarbeitung der Daten ausgegeben.

Insgesamt würde die angepasste Anweisung wie folgt lauten:
Access Beispiel dynamischer VBA Code zum Importieren von Exceltabellen
Entsprechend könnten auch weitere Anweisung innerhalb des VBA Codes angepasst werden und hierdurch eine wesentlich flexiblere Importfunktion gestaltet werden. Darüber hinaus können auch weitere Informationen und Anweisungen zur Handhabung der Datenbank hinterlegt werden.

Als Gesamtcode würde dieses dann in Access 2010 wie folgt aussehen:

Function Import()
 Dim Pfad As String
    Pfad = Application.CurrentProject.Path
    
    If MsgBox("Import aus " & Pfad & " ?", vbYesNo) = vbYes Then
       Pfad = InputBox("Pfadnamen ohne abschliessenden ")
    End If
    
    MsgBox "Import aus " & Pfad & " !", vbInformation, "Importquelle"

    If (MsgBox("Tabelle löschen?", 1) = 1) Then
        DoCmd.DeleteObject acTable, "Grunddaten"
    End If

If (MsgBox("Tabelle importieren", 1) = 1) Then
  DoCmd.TransferSpreadsheet acImport, 10, "Grundtabelle", Pfad & "import.xls", True, "Grunddaten!A:Z"
End If
MsgBox "Daten wurden importiert", vbOKOnly, "Keine Panik"
        If (MsgBox("Auswertungen starten", 1) = 1) Then
            DoCmd.OpenQuery "110 Auswertung 1", acViewNormal, acReadOnly
            DoCmd.OpenQuery "120 Auswertung 2", acViewNormal, acReadOnly
            DoCmd.OpenQuery "130 Auswertung 3", acViewNormal, acReadOnly
MsgBox "Daten über Inhalte Einfügen->Transponieren in die Zieltabelle", vbInformation, "Daten ausgewertet"
End If

Import_Exit:
    Exit Function

Import_Err:
    MsgBox Error$
    Resume Import_Exit

End Function



Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Access

16 Kommentare - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter 🌎 Google
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Alex am 9.8.2014 um 13:01 Uhr
Hey Andreas,

vielen Dank für diese Anleitung. Ich hatte bisher immer nur mit VBA in Excel gearbeitet und aufgrund deiner Darstellung kann ich meine bisherigen Kenntnisse nun auch in Access umstetzen.

Besten Dank und schönes Wochenende

Alex


Anonym am 7.9.2014 um 00:09 Uhr
Sehr hilfreich!


Anonym am 18.9.2014 um 09:46 Uhr
Sehr hilfreich!


Jürgen am 23.10.2014 um 13:34 Uhr
Hallo Andreas,

danke für die leicht verständliche und sofort übernehmbare Aufbereitung. Hat mich mit Sicherheit schneller an's Ziel gebracht.

Viele Grüße, Jürgen


Anonym am 29.10.2014 um 12:26 Uhr
Danke!


Anonym am 7.1.2015 um 13:15 Uhr
Meine Rettung!


Anonym am 12.2.2015 um 14:27 Uhr
Habe ich dringend gesucht!


Anonym am 19.3.2015 um 12:13 Uhr
Habe ich dringend gesucht!


Anonym am 19.3.2015 um 14:36 Uhr
Danke!


Anonym am 8.4.2015 um 15:01 Uhr
Sehr hilfreich!


Anonym am 13.5.2015 um 15:22 Uhr
Sehr hilfreich!


Anonym am 26.5.2015 um 09:02 Uhr
Danke!


Klaus am 2.3.2016 um 07:36 Uhr
Danke!


Anonym am 16.6.2016 um 12:27 Uhr
Danke!


Anonym am 28.9.2016 um 10:57 Uhr
Sehr hilfreich!


Anonym am 17.1.2018 um 10:35 Uhr
Habe ich dringend gesucht!


Kommentare und Anmerkungen

Für diesen Beitrag ist die Kommentarfunktion gesperrt. Sollten Sie eine Anmerkung oder Frage zu diesen Artikel haben freue ich mich über eine Rückmeldung per Mail. Meine Kontaktdaten finden Sie unter Kontakt aber auch am Ende dieser Seite. Neben Mail stehen hier auch verschiedene "soziale Netzwerke" zur Verfügung.



* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste