16:58 Uhr
Dynamisches Importieren von Daten aus Excel nach Access unter Abfrage Speicherpfad per Makro und VBA
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.
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".

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"

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.

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:

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.

Ab Access 2010 befindet sich diese Funktion in der Entwurfsansicht des jeweiligen Makros.

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.

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.

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.

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:
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
ein Angebot von Espresso Tutorials

unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »Dynamisches Importieren von Daten aus Excel nach Access unter Abfrage Speicherpfad per Makro und VBA« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 11.5.2014, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=533 (Abgerufen am 13.2.2025)
16 Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Bluesky 🌎 LinkedIn