Excel automatisieren

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")

Username or Email

Passwort

Angemeldet bleiben