Home > Artikel > Ausgabe 4/2018 > Excel fernsteuern, Teil 2: Workbooks und -sheets

Excel fernsteuern, Teil 2: Workbooks und -sheets

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

Von Access aus können Sie andere Anwendungen per VBA fernsteuern, wenn diese Anwendung dies unterstützt. Das ist grundsätzlich bei den übrigen Office-Anwendungen wie Excel, Outlook, Word oder PowerPoint der Fall. In dieser Artikelserie schauen wir uns an, wie Sie eine Excel-Anwendung fernsteuern können und wie Sie Dokumente anlegen, Daten in eine Excel-Tabelle schreiben oder die Daten auslesen. Im vorliegenden zweiten Teil geht es um den Umgang mit Excel-Dateien, den sogenannten Workbooks, und den darin enthaltenen Tabellen, in der Excel-Welt auch Worksheets genannt.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1804_AccessUndExcelFernsteuernII.accdb.

Begriffsklärung

Normalerweise spricht man ja bei allem, was man in physischer Form im Dateisystem speichern kann, von Dateien oder Verzeichnissen. Unter Excel heißen diese Dateien genauer Workbooks. Jedes Workbook hat im unteren Bereich einige Registerreiter, über die Sie die einzelnen Tabellen eines Workbooks ansteuern können. Wir werden in dieser Artikelreihe die Begriffe Workbook und Worksheet verwenden.

Eine neues Workbook erstellen

Wenn Sie, wie im ersten Teil dieser Artikelreihe (Excel fernsteuern, Teil 1: Initialisierung) erläutert, eine neue Excel-Instanz erstellt haben, können Sie damit noch recht wenig anfangen. Sie benötigen ein Workbook, um etwas zu tun. Dieses können Sie entweder neu erstellen oder Sie öffnen ein bestehendes Workbook. Wir wollen uns zunächst ansehen, wir wir ein neues Workbook erstellen. Wir gehen davon aus, dass wir im gleichen oder in einem anderen Modul eine Objektvariable für Excel deklariert haben:

Public objExcel As Excel.Application

in einem neuen Standardmodul namens mdlWorkbooks legen wir dann die folgende Prozedur an:

Public Sub WorkbookErstellen()

Dim objWorkbook As Excel.Workbook

Set objExcel = New Excel.Application

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add

End Sub

Diese deklariert zunächst eine Objektvariable für das neue Workbook. Danach erzeugen wir die Excel-Instanz und blenden diese durch Einstellen der Variablen Visible auf den Wert True ein. Schließlich folgt der entscheidende Schritt: Das Erstellen des neuen Workbooks durch die Methode Add der Workbooks-Auflistung des Excel.Application-Objekts. Dieses erscheint nun wie in Bild 1 mit einem ersten Worksheet namens Tabelle1, zu erkennen an der Registerkarte im unteren Bereich.

Excel mit einem neuen Workbook

Bild 1: Excel mit einem neuen Workbook

Workbook schließen

Bevor wir uns weitere Möglichkeiten ansehen, schließen wir das Workbook erst einmal wieder per VBA-Code. Das erledigen wir mit der Close-Methode des Workbooks aus objWorkbook:

objWorkbook.Close

Dies schließt das Workbook wieder, bevor wie es beispielsweise in Form einer Datei auf der Festplatte gespeichert haben.

Workbook speichern

Wenn Sie ein Workbook erstellen, wollen Sie es in den meisten Fällen vermutlich auch speichern. Das können Sie beispielsweise erledigen, wenn Sie das Workbook schließen. Das Schließen erfolgt über die Close-Methode. Diese bietet ein paar Parameter an, mit denen Sie festlegen können, ob eventuelle Änderungen gespeichert werden sollen:

  • SaveChanges: Der Wert True gibt an, dass das Workbook beim Schließen gespeichert werden soll, wenn ungespeicherte Änderungen vorliegen.
  • Filename: Gibt den Dateinamen an, unter dem das Workbook gespeichert werden soll.

Ein Beispiel für den Aufruf der Close-Methode sieht nun wie folgt aus:

objWorkbook.Close True, CurrentProject.Path & "\Beispiel.xlsx"

Fügen wir diesen Befehl in die obigen Methode ein, in der wir eine neue Excel-Instanz erstellt und ein Workbook erzeugt haben, finden wir nach der Ausführung allerdings keine neue Datei mit der Dateiendung .xlsx im Verzeichnis der Datenbank vor. Der Grund ist einfach: Wir haben zwar ein Workbook hinzufügt, dieses aber vor dem Schließen nicht geändert. Deshalb wird es beim Schließen auch nicht gespeichert. Dies ändern wir, indem wir ein wenig vorgreifen und die Zelle A1 mit einem Beispieltext füllen, bevor wir die Datei schließen:

Public Sub WorkbookSchliessenSpeichern()

Dim objWorkbook As Excel.Workbook

Set objExcel = New Excel.Application

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add

objWorkbook.Sheets(1).Range("A1") = "Beispieltext"

objWorkbook.Close True, CurrentProject.Path & "\Beispiel.xlsx"

objExcel.Quit

Set objExcel = Nothing

End Sub

Damit landet nun eine Datei namens Beispiel.xlsx in unserem Datenbankverzeichnis. Sollten Sie diese Prozedur erneut aufrufen, fragt Access, ob die vorhandene Datei gleichen Namens überschrieben werden soll.

Es gibt noch weitere Befehle zum Speichern des Workbooks, die ohne das Schließen des Workbooks aufgerufen werden können. Vor allem jedoch speichern diese das Workbook auch dann, wenn Sie noch keine Änderungen vorgenommen haben:

  • Save: Speichert das Workbook unter dem Namen, unter dem es bereits zuvor gespeichert wurde oder unter dem Namen Mappe1.xlsx.
  • SaveAs: Speichert das Workbook unter dem als Parameter angegebenen Namen. Diese Anweisung bietet allerdings noch viele weitere Parameter, etwa zum Festlegen des Dateiformats.

Für den Parameter zur Angabe des Dateiformats verwenden Sie etwa die folgenden Werte:

  • xlExcel8: Excel-Workbook im Format von Excel 97-2003
  • xlExcel12: Excel-Workbook (binär) im Format von Excel 2007-2013
  • xlOpenXMLWorkbook: Excel-Workbook (XML) im Format von Excel 2007-2016
  • xlOpenXMLWorkbookMacroEnabled: Excel-Workbook (XML) mit Makros im Format von Excel 2007-2016

Zu beachten ist hier, dass Sie jeweils die passende Dateiendung zur Excel-Version angeben sollten, also .xls für Dateien bis Excel 2003 und .xlsx für Dateien ab Excel 2007.

Workbook ohne Umweg über das Excel-Objekt erstellen

Die obige Methode geht davon aus, dass Sie ein Objekt des Typs Excel.Application erzeugen und damit dann ein Workbook-Objekt anlegen. Das ist immer dann sinnvoll, wenn Sie das Excel-Objekt für mehr als das pure Anlegen benötigen – etwa, um noch weitere Workbooks zu erzeugen.

Wenn Sie nur das Workbook erzeugen möchten, aber keine Verwendung für die Objektvariable des Typs Excel.Application haben, können Sie die folgende Variante nutzen:

Public Sub WorkbookCreate()

Dim objWorkbook As Excel.Workbook

Set objWorkbook = CreateObject("Excel.Sheet")

objWorkbook.SaveAs CurrentProject.Path & "\CreateObject.xlsx"

Set objWorkbook = Nothing

End Sub

Hier erstellen wir ein neues Objekt des Typs Excel.Workbook, interessanterweise aber mit dem Parameter Excel.Sheet für die CreateObject-Methode. Die Methode funktioniert jedoch einwandfrei und ist gefühlt auch etwas schneller als der Weg über das vorherige Erstellen der Excel-Instanz.

Es gibt allerdings eine Excel-Instanz, nur dass wir diese nicht per Objektvariable referenziert haben. Das wird deutlich, wenn wir versuchen, auch das wie oben erstellte Workbook anzuzeigen. Die Workbook-Klasse selbst liefert keine Eigenschaft namens Vislble, mit der wir die Excel-Instanz einblenden könnten. Wir müssen also wohl diese Eigenschaft des übergeordneten Objekts des Typs Excel.Application einstellen. Das übergeordnete Objekt referenzieren wir über die Eigenschaft Parent des Workbook-Objekts:

Public Sub WorkbookCreate()

Dim objWorkbook As Excel.Workbook

Set objWorkbook = CreateObject("Excel.Sheet")

objWorkbook.SaveAs CurrentProject.Path & "\CreateObject.xlsx"

objWorkbook.Parent.Visible = True

Stop

Set objWorkbook = Nothing

End Sub

Die Stop-Anweisung haben wir eingebaut, damit Sie sich von der Existens der Excel-Instanz überzeugen können.

Bestehende Excel-Datei öffnen

Nun, da wir ein paar Excel-Dateien auf die Festplatte gebannt haben, wollen wir diese per VBA erneut öffnen und gegebenenfalls auch anzeigen. Um bei Vorhandensein einer Objektvariablen mit einer Excel-Instanz ein Workbook zu öffnen, nutzen wir die Open-Methode der Workbooks-Auflistung:

Public Sub WorkbookOeffnenInstanz()

Dim objWorkbook As Excel.Workbook

Set objExcel = New Excel.Application

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open( CurrentProject.Path & "\Beispiel.xlsx")

End Sub

Dies zeigt das unter dem Dateinamen Beispiel.xlsx gespeicherte Workbook an.

Geöffnetes Workbook referenzieren

Wenn Sie bereits eine Excel-Instanz mit einem Workbook geöffnet haben und dieses per VBA referenzieren möchten, um darauf zuzugreifen oder Änderungen vorzunehmen, können Sie die Referenzierung mit der GetObject-Methode durchführen. Dazu übergeben Sie dieser einfach den Pfad zu der Excel-Datei:

Public Sub WorkbookOeffnenCreateObject()

Dim objWorkbook As Excel.Workbook

Set objWorkbook = GetObject(CurrentProject.Path & "\Beispiel.xlsx")

objWorkbook.Parent.Visible = True

objWorkbook.Sheets(1).Range("A2") = "Neuer Text"

End Sub

In diesem Fall fügen wir noch einen Wert zur Zelle A2 hinzu (siehe Bild 2).

Referenzieren eines geöffneten Workbooks

Bild 2: Referenzieren eines geöffneten Workbooks

Schnell auf Workbooks zugreifen

Im ersten Teil der Artikelreihe haben wir bereits die Funktion GetExcel vorgestellt, mit der Sie schnell eine neue Excel-Instanz hervorzaubern und nutzen können. Genau genommen verkürzen Sie damit gar nicht die Zeit, bis die Excel-Instanz zur Verfügung steht, aber wir haben den dafür notwendigen Code ausgelagert und Sie brauchen diesen nicht immer neu zu schreiben. Das wollen wir nun auch für ein Excel-Workbook machen, und zwar zum Erstellen eines neuen Workbooks und auch zum Öffnen bestehender Workbooks. Beides sollte möglichst durch eine einzige Funktion abgedeckt werden, der wir den Pfad der zu öffnenden beziehungsweise der zu erstellenden Workbook-Datei übergeben. Diese Funktion fügen wir wieder dem Standardmodul mdlExcelobjekt hinzu, das wir auch bereits im ersten Teil angelegt haben:

Public Function GetWorkbook(strWorkbook As String) As Excel.Workbook

Dim objWorkbook As Excel.Workbook

If Len(Dir(strWorkbook)) = 0 Then

Set objWorkbook = GetExcel.Workbooks.Add()

objWorkbook.SaveAs strWorkbook

Else

Set objWorkbook = GetExcel.Workbooks.Open(strWorkbook)

End If

Set GetWorkbook = objWorkbook

End Function

Die Funktion erwartet den Pfad der zu erstellenden oder zu öffnenden Workbook-Datei als Parameter. Sie prüft dann im ersten Schritt mit der Dir-Funktion, ob es bereits eine Datei mit diesem Pfad gibt. Ist das nicht der Fall, verwendet sie die Add-Methode der Workbooks-Auflistung, um die Datei zu erstellen und speichert diese direkt im Anschluss mit der SaveAs-Methode unter dem angegebenen Dateinamen. Ist die Datei hingegen bereits vorhanden, öffnet die Funktion diese mit der Open-Methode unter Angabe des Pfades. In beiden Fällen landet ein Verweis auf das geöffnete Excel-Workbook in der Variablen objWorkbook, die dann als Funktionswert von GetWorkbook zurückgegeben wird.

Ein Aufruf dieser Funktion sieht dann etwa wie folgt aus:

Set objWorkbook = GetWorkbook("c:\Workbook.xlsx")

Zu beachten ist hier, dass das Workbook geöffnet bleibt, wenn Sie es nicht explizit wieder schließen. Auch die entsprechende Excel-Instanz bleibt im Hintergrund geöffnet, auch wenn Sie die Datenbankanwendung schließen und somit auch die Objektvariable leeren.

Probleme mit GetExcel

Auch mit GetExcel könnte es Probleme geben, wenn Sie mit dieser Funktion eine Excel-Instanz erzeugen, diese dann im Task-Manager beenden und anschließend nochmals auf die mit GetExcel unter der Variablen m_Excel gespeicherten Instanz zugreifen wollen. Diese Instanzvariable wird nämlich nicht geleert, wenn die Instanz selbst von außerhalb beendet wird. Wenn dies nicht zu Testzwecken geschieht, sollte eine solche über diese Funktion erzeugte Excel-Instanz allerdings auch nicht über den Task-Manager beendet werden. Wir können diesen Fehler allerdings auch verhindern, indem wir die Funktion GetExcel wie folgt erweitern:

Public Function GetExcel() As Excel.Application

Dim strTest As String

If m_Excel Is Nothing Then

Set m_Excel = New Excel.Application

Else

On Error Resume Next

strTest = m_Excel.Name

If Err.Number = 462 Then

Set m_Excel = New Excel.Application

End If

On Error GoTo 0

End If

Set GetExcel = m_Excel

End Function

Wir versuchen hier, wenn m_Excel gefüllt ist, also eine Instanz existieren sollte, im Else-Zweig der If...Then-Bedingung auf den Namen der Excel-Instanz zuzugegreifen. Wenn die Instanz zwischenzeitlich von außerhalb beendet wurde, löst dies den Fehler 462 aus (Der Remote-Server-Computer existiert nicht oder ist nicht verfügbar.). In diesem Fall erstellen wir die Instanz einfach neu und weisen diese wiederum der Variablen m_Excel zu, die dann als Rückgabewert der Funktion zurückgeliefert wird.

Mehrere Workbooks

Wenn Sie ein Workbook in einer Excel-Instanz öffnen und dann von dieser Instanz aus über den Öffnen-Befehl ein weiteres Workbook, sieht es zwar so aus, als ob zwei Instanzen von Excel geöffnet wurden (siehe Bild 3). In der Tat handelt es sich hierbei allerdings nur um eine einzige Instanz, die zwei Workbooks geöffnet hat. Das soll nicht darüber hinwegtäuschen, dass auch zwei Instanzen mit jeweils einem geöffneten Workbook genauso aussehen wie diese eine Instanz mit zwei Workbooks! Unter VBA erzeugen Sie eine Instanz mit zwei Workbooks etwa wie folgt:

Mehrere Workbooks in einer Instanz

Bild 3: Mehrere Workbooks in einer Instanz

Public Sub ZweiWorkbooks()

Dim objExcel As Excel.Application

Dim objWorkbook1 As Excel.Workbook

Dim objWorkbook2 As Excel.Workbook

Dim objWorkbook As Excel.Workbook

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!