Home > Artikel > Ausgabe 3/2018 > Exceldaten per SQL und DAO

Exceldaten per SQL und DAO

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

Im der Artikelreihe »Excel fernsteuern« haben Sie erfahren, wie Sie per Automation auf eine Excel-Datei zugreifen und dabei Daten aus der Tabelle lesen, schreiben oder bearbeiten können. Die Daten Zelle für Zelle zu durchlaufen ist dabei in vielen Fällen unumgänglich, vor allem, wenn Sie dabei auch noch individuelle Formatierungen unterbringen wollen. Wenn Sie jedoch schnell Daten von Excel nach Access oder umgekehrt bewegen wollen, ohne eine Excel-Instanz zu erzeugen und keine besonderen Anforderungen an die Formatierung haben, gibt es eine praktische Alternative – und zwar eine, bei der Sie sogar von Ihren vorhandenen DAO-Kenntnissen profitieren können.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1803_ExceldatenPerSQLUndDAO.accdb.

Excel-Zugriff per OpenRecordset und SELECT

Wenn Sie per DAO auf die Daten einer Datenbank zugreifen wollen, um diese entweder per VBA zu durchlaufen oder diese zu löschen, anzulegen oder zu bearbeiten, beginnen Sie in der Regel mit dem Öffnen eines Recordset-Objekts auf Basis der Tabelle oder Abfrage, in der sich die Daten befinden, die durchlaufen oder bearbeitet werden sollen. Die Syntax für die dazu verwendete OpenRecordset-Methode des Database-Objekts ist dabei hinlänglich bekannt – Sie benötigen zwei Objektvariablen namens db und rst und dann geht es los:

Dim db As DAO.Database

Dim rst As DAO.Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM tblArtikel", dbOpenDynaset)

'... etwas mit den Datensätzen machen

Benötigte Excel-Informationen

Aber wie können wir das nun für den Zugriff auf die Daten einer Excel-Tabelle wie etwa der aus Bild 1 nutzen? Dazu benötigen wir nur einige wenige Informationen über die Excel-Daten, auf die wir zugreifen wollen:

Formular-Entwurf unseres Beispielformulars

Bild 1: Formular-Entwurf unseres Beispielformulars

  • den Dateinamen inklusive Verzeichnis,
  • den Namen der Tabelle und
  • den Bereich, auf den wir zugreifen wollen.

Den Rest bekommen wir durch geschickte Formulierung der SELECT-Abfrage hin. Diese ist gar nicht so viel komplizierter aufgebaut wie eine herkömmliche SELECT-Anweisung – wenn man weiß, wie es geht. Ein Beispiel ist das folgende:

SELECT * FROM [Artikelliste$A:J] IN 'C:\...\Artikel.xlsx'[Excel 8.0;HDR=No;IMEX=0;]

Diese Abfrage erwartet hinter dem FROM-Schlüsselwort den Namen der Tabelle in der Excel-Datei (hier Artikelliste)und davon durch ein Dollar-Zeichen ($) getrennt den betroffenen Bereich, hier A:J. Dieser Ausdruck muss in eckigen Klammern zusammengefasst werden. Danach müssen wir allerdings noch angeben, in welcher Datei sich die Daten befinden. Diese geben wir mit dem IN-Schlüsselwort an.

Dieses erwartet zwei Bestandteile. Als Erstes den Dateinamen in Hochkommata ('C:\...\Artikel.xlsx') und als zweites in eckigen Klammern Informationen darüber, in welchem Format die gelieferten Daten vorliegen ([Excel 8.0;HDR=No;IMEX=0;]).

Mit HDR geben Sie an, ob beim Zugriff vom Vorhandensein von Spaltenüberschriften in der ersten Zeile ausgegangen werden soll. In unseren Tests machte es allerdings keinen Unterschied – wir erhielten sowohl mit Yes als auch mit No nur die gewünschten Daten zurück und nicht die Spaltenüberschriften.

Die Prozedur aus Listing 1 zeigt, wie Sie die verschiedenen Parameter wie Dateiname, Tabellenname und Bereich einzeln in Variablen erfassen und diese dann zu einer SELECT-Anweisung zusammenstellen.

Public Sub ExcelPerRecordset()

     Dim db As DAO.Database

     Dim rst As DAO.Recordset

     Dim strDatei As String

     Dim strTabelle As String

     Dim strBereich As String

     Dim strSQL As String

     Set db = CurrentDb

     strDatei = CurrentProject.Path & "\Artikel.xlsx"

     strTabelle = "Artikelliste"

     strBereich = "A:J"

     strSQL = "SELECT * FROM [" & strTabelle & "$" & strBereich & "] IN '" & strDatei & "'[Excel 8.0;HDR=Yes;IMEX=0;]"

     Debug.Print strSQL

     Set rst = db.OpenRecordset(strSQL)

     Debug.Print rst.Fields(0).Name, rst.Fields(1).Name

     Do While Not rst.EOF

         Debug.Print rst.Fields(0), rst.Fields(1)

         rst.MoveNext

     Loop

End Sub

Listing 1: Diese Prozedur erstellt ein Recordset auf Basis einer Excel-Tabelle und gibt die enthaltenen Daten aus.

Diese wird dann als Parameter der OpenRecordset-Methode verwendet. Die folgenden Anweisungen durchlaufen in einer Do While-Schleife alle Datensätze des angegbenen Bereichs und geben die Daten im Direktfenster aus. Das Ergebnis sehen Sie schließlich in Bild 2.

Ausgabe der Datensätze einer Excel-Datei per Schleife

Bild 2: Ausgabe der Datensätze einer Excel-Datei per Schleife

Daten mit DAO bearbeiten

Wenn wir schon mit einem Recordset auf die Daten der Excel-Tabelle zugreifen, können wir dann nicht auch gleich die DAO-Methoden AddNew, Edit oder Update verwenden, um die enthaltenen Daten zu bearbeiten? Wir schauen uns an, ob dies funktioniert. Dazu verwenden wir eine ähnlich aufgebaute Prozedur wie die aus dem vorherigen Beispiel. Hier ergänzen wir jedoch den Ausdruck für die Variable strSQL ein wenig, indem wir dieser hinten noch die folgende WHERE-Klausel anhängen:

... WHERE ArtikelID = 1

Die Bedingung müssen Sie bei solchen Abfragen auf Basis einer Excel-Tabelle noch hinter der Angabe IN-Klausel mit der Quelldatei einfügen. Danach erstellen wir wieder ein Recordset auf Basis der Abfrage aus strSQL und referenzieren es mit der Variablen rst. Deren Methode Edit nutzen wir, um den aktuellen Datensatz zum Bearbeiten vorzubereiten. Danach können wir beispielsweise dem Feld Artikelname einen neuen Wert zuweisen und die Änderung dann durch Aufrufen der Update-Methode in der zugrunde liegenden Datenquelle speichern (siehe Listing 2).

Public Sub ExcelPerRecordsetBearbeiten()

     '... wie vorheriges Beispiel

     strSQL = "SELECT * FROM [" & strTabelle & "$" & strBereich & "] IN '" & strDatei & "'[Excel 8.0;HDR=Yes;IMEX=0;] WHERE ArtikelID = 1"

     Debug.Print strSQL

     Set rst = db.OpenRecordset(strSQL)

     rst.Edit

     rst!Artikelname = "Chai (Neu)"

     rst.Update

End Sub

Listing 2: Editieren eines Datensatzes per Edit/Update-Methode

Nach dem Ausführen dieser Prozedur finden Sie den bearbeiteten Datensatz in der Excel-Tabelle mit dem neuen Artikelnamen vor (siehe Bild 3).

Geänderter Zellwert in einer Excel-Tabelle

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!