Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Es kommt immer wieder vor, dass Sie als Access-Entwickler Daten aus Excel-Dateien lesen oder auch in Excel-Tabellen schreiben müssen. Excel lässt sich genau wie die übrigen Office-Anwendungen per VBA steuern, sodass Sie Excel für den Zugriff auf die Daten in einer Excel-Datei nicht manuell öffnen müssen. Wie dies funktioniert und was Sie alles anstellen können, erfahren Sie in diesem Grundlagenbeitrag.
Für die Automatisierung von Excel von Access aus kann es verschiedene Gründe geben. Im Wesentlichen teilen sich diese auf die folgenden beiden Gruppen auf:
- Sie möchten Daten aus einer Excel-Datei auslesen.
- Sie möchten eine Excel-Datei mit Daten füllen oder die enthaltenen Daten bearbeiten und dafür gegebenenfalls zunächst eine neue Excel-Datei erstellen.
Zugriff auf Excel
In beiden Fällen greifen Sie auf eine Excel-Instanz und die damit geöffnete Excel-Datei zu. Dies können Sie, wie bei Automation üblich, auf zwei Arten tun: Sie setzen einen Verweis auf die Bibliothek Microsoft Excel x.0 Object Library und greifen danach bequem per IntelliSense auf die VBA-Objekte der Excel-Bibliothek zu (Early Binding) oder Sie deklarieren die verwendeten Variablen mit dem Datentyp Object, anstatt die in der Excel-Bibliothek enthaltenen Objekte zu verwenden (Late Binding). Für unsere Zwecke soll Early Binding zum Einsatz kommen.
Daher stellen Sie im Verweise-Dialog einen entsprechenden Verweis wie in Bild 1 ein. Öffnen Sie dazu den VBA-Editor (Alt + F11) und wählen Sie den Menüeintrag Extras|Verweise aus.
Bild 1: Verweis auf die Excel-Bibliothek im Verweise-Dialog des VBA-Editors
Manchmal kann es sinnvoll sein, später auf Late Binding umzustellen, der besseren Lesbarkeit des Codes halber verzichten wir an dieser Stelle aber darauf.
Excel-Instanz erzeugen
Bevor Sie eine Excel-Instanz erzeugen können, brauchen Sie eine Variable, in der Sie diese speichern können:
Dim objExcel As Excel.Application
Danach können Sie mit der folgenden Anweisung eine neue Instanz erzeugen:
Set objExcel = New Excel.Application
Das Setzen der Instanzvariablen auf Nothing schließt gleichzeitig eine so erzeugte Excel-Instanz:
Set objExcel = New Excel.Application
Vielleicht möchten Sie auch auf eine bestehende Excel-Instanz zugreifen, um beispielsweise ein durch den Benutzer geöffnetes Excel-Fenster fernzusteuern. Dies erledigen Sie mit dieser Anweisung:
Set objExcel = GetObject(, "Excel.Application")
Das funktioniert aber nur, wenn auch eine Excel-Instanz vorliegt. Wenn nicht, erhalten Sie eine Fehlermeldung mit der Nummer 429 und dem Text Objekterstellung durch ActiveX-Komponente nicht möglich (siehe Bild 2). Diesen Fehler können Sie im Code beispielsweise so abfangen:
Bild 2: Diese Fehlermeldung erscheint beim Versuch, eine nicht vorhandene Excel-Instanz zu referenzieren.
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then
MsgBox "Excel ist nicht geöffnet."
End If
Wenn bei nicht vorhandener Excel-Instanz eine neue geöffnet werden soll, erweitern Sie den Code wie folgt:
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then
Set objExcel = _
CreateObject("Excel.Application")
End If
On Error GoTo 0
objExcel enthält dann auf jeden Fall einen Verweis auf eine Excel-Instanz – vorausgesetzt, dass Excel überhaupt auf dem Rechner installiert ist. Nun haben Sie allerdings zwei verschiedene Methoden gesehen, um eine frische Excel-Instanz zu erzeugen:
Set objExcel = New Excel.Application
und
Set objExcel = CreateObject("Excel.Application")
Wo ist hier der Unterschied Beide Anweisungen erledigen die gleiche Aufgabe, allerdings funktioniert die erste nur, wenn Sie mit Early Binding arbeiten, also ein Verweis auf die Excel-Bibliothek vorhanden ist.
An dieser Stelle sei auch auch noch etwas zum Übernehmen einer bestehenden Excel-Instanz angemerkt: Sie sollten dies nur tun, wenn es die Situation unbedingt erfordert, wofür es kaum Beispiele gibt. Gegebenenfalls könnte man es mit Performance-Gründen rechtfertigen, aber bei der heutigen Rechenleistung sollte ein Rechner zwei oder mehr parallel geöffnete Excel-Instanzen verkraften können.
Wenn Sie für Ihre Aufgabe eine eigene Excel-Instanz öffnen und diese nicht sichtbar machen, können Sie davon ausgehen, dass der Benutzer nicht anderweitig auf diese zugreift. Daher können Sie diese Instanz auch ohne Gewissensbisse wieder schließen – Sie nehmen sie niemandem weg.
Und wenn Sie sich davon überzeugen möchten, dass Windows tatsächlich eine neue Excel-Instanz erzeugt und keine vorhandene Instanz verwendet, wenn Sie per Code eine neue Instanz erzeugen, können Sie das Experiment aus Bild 3 ausführen und sich im Task-Manager davon überzeugen, dass mehrere Excel-Instanzen existieren.
Bild 3: Öffnen mehrere Excel-Instanzen per Code und ihre Pendants im Task-Manager
Hier brauchen Sie im Übrigen nur die Excel-Objektvariablen durch Setzen auf Nothing zu leeren, um die Excel-Instanzen zu beenden – später erfahren Sie, dass dies nicht immer ausreicht.
Funktion zum Erzeugen einer Excel-Instanz
Manchmal brauchen Sie eine Excel-Instanz nur ganz kurz, um etwa ein Excel-Workbook zu öffnen, manchmal arbeiten Sie auch länger mit dieser Instanz.
In beiden Fällen kann es nicht schaden, den Code zum Erzeugen der Excel-Instanz in eine kleine Funktion auszulagern. Die Excel-Instanz selbst speichern wir in einer Objektvariablen in einem Standardmodul, dass beispielsweise mdlExcel heißen könnte:
Dim mExcel As Excel.Application
Dazu bauen wir uns eine kleine Funktion, die schaut, ob bereits eine Excel-Instanz in mExcel gespeichert wurde und dies gegebenenfalls nachholt. Der in mExcel gespeicherte Verweis wir in jedem Fall als Übergabewert der Funktion GetExcel zurückgeliefert:
Public Function GetExcel() As _
Excel.Application
If mExcel Is Nothing Then
Set mExcel = _
New Excel.Application
End If
Set GetExcel = mExcel
End Function
Excel-Datei erzeugen
Wenn Sie in objExcel eine Referenz auf eine Excel-Instanz gespeichert haben, können Sie damit ein neues Dokument erzeugen. Dafür deklarieren Sie zunächst eine entsprechende Variable:
Dim objWorkbook As Excel.Workbook
Danach können Sie diese Variable durch Verwenden der Add-Anweisung der Workbooks-Auflistung des Application-Objekts erstellen:
Set objWorkbook = objExcel.Workbooks.Add
Nun können Sie die Excel-Datei mit den weiter unten beschriebenen Methoden bearbeiten und diese dann schließen:
objWorkbook.Close
Das ist unbedingt erforderlich, denn sonst reicht die folgende Anweisung nicht aus, um die Excel-Instanz zu beenden:
Set objExcel = Nothing
Alternativ können Sie Excel vor dem Leeren der Variablen auch explizit per Code schließen:
objExcel.Quit
Es gibt aber auch noch eine wesentlich direktere Methode, die Sie vor allem dann nutzen sollten, wenn Sie nur auf das Workbook und nicht direkt auf die Excel-Instanz zugreifen möchten:
Set objWorkbook = CreateObject("Excel.Sheet")
Excel sichtbar machen
Alles, was wir in den bisherigen Beispielen mit Excel erledigt haben (was ja nicht viel war), geschah im Hintergrund, also ohne Anzeigen des Excel-Fensters.
Bevor wir gleich in den Zugriff auf Excel-Dateien einsteigen, erfahren Sie noch, wie Sie die Excel-Instanz sichtbar machen. Dies erledigt die folgende Anweisung:
objExcel.Visible = True
Excel-Datei öffnen
Für den Zugriff auf eine bestehende Excel-Datei verwenden Sie die folgende Anweisung, die auf der oben genannten Vorgehensweise zum Erzeugen oder Holen einer Excel-Instanz basiert.
Mit dieser Instanz können Sie dann Folgendes tun:
Set objWorkbook = objExcel.Workbooks.Open("c:\Beispiel.xls")
Auch hier gibt es eine zweite Variante. Dabei übergeben Sie einfach den Dateinamen an die CreateObject-Methode:
Set objWorkbook = CreateObject("c:\Beispiel.xls")
Funktion zum einfachen Öffnen einer Excel-Datei
Genau wie für das Erzeugen einer Excel-Instanz wollen wir auch eine einfache Funktion zum Öffnen eines Excel-Workbooks programmieren.
Diese setzt auf der oben bereits vorgestellten Funktion GetExcel auf und und öffnet die als Parameter angegebene Datei in der damit verfügbar gemachten Excel-Instanz:
Public Function GetWorkbook(strPath As String) _
As Excel.Workbook
Dim objWorkbook As Excel.Workbook
Set objWorkbook = _
GetExcel.Workbooks.Open(strPath)
Set GetWorkbook = objWorkbook
End Function
Wenn Sie ein Workbook öffnen und in einer Prozedur damit arbeiten möchten, brauchen Sie also nur eine entsprechende Objektvariable zu deklarieren und diese mit Hilfe der Funktion GetWorkbook zu füllen:
Dim objWorkbook As Excel.Workbook
Set objWorkbook = GetWorkbook("c:\Beispiel.xls")
Um die nachfolgenden Beispiele kurz zu halten, greifen wir immer wieder auf diese beiden Zeilen zurück.
Ein oder mehrere Workbooks
Eine Excel-Instanz kann ein oder mehrere Workbooks enthalten. Diese lassen sich mit der Workbooks-Auflistung des Application-Objekts referenzieren.
In den folgenden Beispielen gehen wir jedoch davon aus, dass wir immer nur ein Workbook in einer frischen Excel-Instanz öffnen und lesend oder schreibend darauf zugreifen.
Zugriff auf Tabellenblätter
Ein Verweis auf die Excel-Datei, in diesem Fall repräsentiert durch einen Objektverweis des Typs Excel.Workbook, reicht noch nicht aus, um auf die Inhalte der Zellen zuzugreifen.
Jedes Workbook enthält nämlich verschiedene Worksheet-Objekte – das sind die einzelnen Tabellenblätter, die Sie über die Registerkartenreiter unter der Tabelle auswählen können (siehe Bild 4).
Bild 4: Excel-Datei mit den drei standardmäßig vorhandenen Sheets
Mit den folgenden Zeilen öffnen Sie eine Excel-Datei und durchlaufen alle enthaltenen Sheets, wobei Sie deren Namen im Direktfenster ausgeben:
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objWorkbook = GetWorkbook("c:\Beispiel.xls")
For Each objSheet In objWorkbook.Worksheets
Debug.Print objSheet.Name
Next objSheet
Der direkte Zugriff auf eines der Worksheets erfolgt entweder über den Index oder die Bezeichnung. Bei Verwendung des Index ist zu beachten, dass dieser immer bei 1 beginnt und nicht bei 0:
Debug.Print objWorkbook.Worksheets(1).Name
Das Gleiche funktioniert auch direkt über den Namen:
Debug.Print objWorkbook.Worksheets("Tabelle1").Name
Wenn Sie Operationen in einem einzigen Worksheet ausführen möchten, speichern Sie einen Verweis darauf in einer Variablen des Typs Excel.Worksheet und greifen dann auf dieses zu.
Zellen lesen und beschreiben
Egal, ob Sie lesend oder schreibend auf die Zellen eines Tabellenblatts unter Excel zugreifen möchten: Die Vorgehensweise ist fast identisch. Zur Veranschaulichung der folgenden Beispiele verwenden wir die Excel-Tabelle mit den Daten aus Bild 5 (alle Zellen sind als Text formatiert).
Bild 5: Beispielfüllungen in einer Excel-Tabelle
Wenn Sie etwa den Inhalt der ersten Zelle in eine Variable einlesen möchten, verwenden Sie die folgende Anweisung (wir gehen davon aus, dass Sie die Objektvariable objSheet zuvor mit einer der oben genannten Methoden mit einem Verweis auf das betreffende Tabellenblatt gefüllt haben):
Debug.Print objSheet.Cells(1, 1) 1,1/A1
Der erste Parameter der Cells-Funktion erwartet die Angabe des Spaltenindex, der zweite den Zeilenindex.
Die Cells-Funktion liefert immer eine einzige Zelle zurück. Die Range-Funktion kann noch mehr, aber zunächst schauen wir uns an, wie Sie mit ihr auf den Inhalt einer Zelle zugreifen:
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo