Verwenden des Arbeitsmappenobjekts in Excel VBA (Öffnen, Schließen, Speichern, Festlegen)

In diesem Tutorial werde ich die Arbeit mit Arbeitsmappen in Excel mit VBA behandeln.

In Excel ist eine „Arbeitsmappe“ ein Objekt, das Teil der Sammlung „Arbeitsmappen“ ist. Innerhalb einer Arbeitsmappe haben Sie verschiedene Objekte wie Arbeitsblätter, Diagrammblätter, Zellen und Bereiche, Diagrammobjekte, Formen usw.

Mit VBA können Sie mit einem Arbeitsmappenobjekt viele Dinge tun - z. B. eine bestimmte Arbeitsmappe öffnen, Arbeitsmappen speichern und schließen, neue Arbeitsmappen erstellen, die Arbeitsmappeneigenschaften ändern usw.

Also lasst uns anfangen.

Alle Codes, die ich in diesem Tutorial erwähne, müssen im Visual Basic-Editor platziert werden. Gehen Sie zum Abschnitt "Wo soll der VBA-Code platziert werden", um zu erfahren, wie es funktioniert.

Wenn Sie daran interessiert sind, VBA auf einfache Weise zu lernen, besuchen Sie mein Online Excel VBA-Schulung.

Verweisen auf eine Arbeitsmappe mit VBA

Es gibt verschiedene Möglichkeiten, in VBA auf ein Arbeitsmappenobjekt zu verweisen. Welche Methode Sie wählen, hängt davon ab, was Sie erreichen möchten. In diesem Abschnitt werde ich die verschiedenen Möglichkeiten zum Verweisen auf eine Arbeitsmappe zusammen mit einigen Beispielcodes behandeln.

Verwenden von Arbeitsmappennamen

Wenn Sie den genauen Namen der Arbeitsmappe haben, auf die Sie verweisen möchten, können Sie den Namen im Code verwenden.

Beginnen wir mit einem einfachen Beispiel.

Wenn Sie zwei Arbeitsmappen geöffnet haben und die Arbeitsmappe mit dem Namen "Examples.xlsx" aktivieren möchten, können Sie den folgenden Code verwenden:

Sub ActivateWorkbook() Workbooks("Examples.xlsx").Activate End Sub

Beachten Sie, dass Sie den Dateinamen zusammen mit der Erweiterung verwenden müssen, wenn die Datei gespeichert wurde. Wenn es nicht gespeichert wurde, können Sie den Namen ohne die Dateierweiterung verwenden.

Wenn Sie sich nicht sicher sind, welchen Namen Sie verwenden sollen, verwenden Sie den Projekt-Explorer.

Wenn Sie eine Arbeitsmappe aktivieren und eine bestimmte Zelle in einem Arbeitsblatt in dieser Arbeitsmappe auswählen möchten, müssen Sie die gesamte Adresse der Zelle (einschließlich der Arbeitsmappe und des Arbeitsblattnamens) angeben.

Sub ActivateWorkbook() Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate Range("A1").Select End Sub

Der obige Code aktiviert zuerst Sheet1 in der Arbeitsmappe "Examples.xlsx" und wählt dann Zelle A1 im Blatt aus.

Sie werden oft einen Code sehen, in dem ein Verweis auf ein Arbeitsblatt oder eine Zelle/einen Bereich erstellt wird, ohne auf die Arbeitsmappe zu verweisen. Dies geschieht, wenn Sie auf das Arbeitsblatt/die Bereiche in derselben Arbeitsmappe verweisen, die den Code enthält und auch die aktive Arbeitsmappe ist. In einigen Fällen müssen Sie jedoch die Arbeitsmappe angeben, um sicherzustellen, dass der Code funktioniert (mehr dazu im Abschnitt DieseArbeitsmappe).

Verwenden von Indexnummern

Sie können auch anhand ihrer Indexnummer auf die Arbeitsmappen verweisen.

Wenn Sie beispielsweise drei Arbeitsmappen geöffnet haben, zeigt Ihnen der folgende Code die Namen der drei Arbeitsmappen in einem Meldungsfeld (nacheinander) an.

Sub WorkbookName() MsgBox Workbooks(1).Name MsgBox Workbooks(2).Name MsgBox Workbooks(3).Name End Sub

Der obige Code verwendet MsgBox – eine Funktion, die ein Meldungsfeld mit dem angegebenen Text/Wert anzeigt (in diesem Fall der Name der Arbeitsmappe).

Eines der Probleme, die ich oft bei der Verwendung von Indexnummern mit Arbeitsmappen habe, besteht darin, dass Sie nie wissen, welche Arbeitsmappe die erste und welche die zweite ist und so weiter. Um sicher zu sein, müssten Sie den Code wie oben gezeigt oder etwas Ähnliches ausführen, um die geöffneten Arbeitsmappen zu durchlaufen und ihre Indexnummer zu kennen.

Excel behandelt die zuerst geöffnete Arbeitsmappe mit der Indexnummer 1 und die nächste mit 2 usw.

Trotz dieses Nachteils kann sich die Verwendung von Indexnummern als nützlich erweisen. Wenn Sie beispielsweise alle geöffneten Arbeitsmappen durchlaufen und alle speichern möchten, können Sie die Indexnummern verwenden. Da dies bei allen Arbeitsmappen der Fall sein soll, müssen Sie sich in diesem Fall nicht um deren individuelle Indexnummern kümmern.

Der folgende Code würde alle geöffneten Arbeitsmappen durchlaufen und alle außer der Arbeitsmappe schließen, die diesen VBA-Code enthält.

Sub CloseWorkbooks() Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks(i).Name ThisWorkbook.Name Then Workbooks(i).Close End If Next i End Sub

Der obige Code zählt die Anzahl der geöffneten Arbeitsmappen und durchläuft dann alle Arbeitsmappen mithilfe der For Each-Schleife.

Es verwendet die IF-Bedingung, um zu überprüfen, ob der Name der Arbeitsmappe mit dem der Arbeitsmappe übereinstimmt, in der der Code ausgeführt wird.

Wenn es keine Übereinstimmung gibt, schließt es die Arbeitsmappe und wechselt zur nächsten.

Beachten Sie, dass wir die Schleife von WbCount bis 1 mit einem Schritt von -1 ausgeführt haben. Dies geschieht, da mit jeder Schleife die Anzahl der geöffneten Arbeitsmappen abnimmt.

Diese Arbeitsmappe wird im späteren Abschnitt ausführlich behandelt.

Verwenden von ActiveWorkbook

ActiveWorkbook bezieht sich, wie der Name schon sagt, auf die aktive Arbeitsmappe.

Der folgende Code würde Ihnen den Namen der aktiven Arbeitsmappe anzeigen.

Sub ActiveWorkbookName() MsgBox ActiveWorkbook.Name End Sub

Wenn Sie VBA verwenden, um eine andere Arbeitsmappe zu aktivieren, verweist der ActiveWorkbook-Teil in der VBA danach auf die aktivierte Arbeitsmappe.

Hier ist ein Beispiel dafür.

Wenn eine Arbeitsmappe aktiv ist und Sie den folgenden Code einfügen und ausführen, wird zuerst der Name der Arbeitsmappe angezeigt, die den Code enthält, und dann den Namen von Example.xlsx (die durch den Code aktiviert wird).

Sub ActiveWorkbookName() MsgBox ActiveWorkbook.Name Workbooks("Examples.xlsx").Activate MsgBox ActiveWorkbook.Name End Sub

Beachten Sie, dass beim Erstellen einer neuen Arbeitsmappe mit VBA diese neu erstellte Arbeitsmappe automatisch zur aktiven Arbeitsmappe wird.

Verwenden dieser Arbeitsmappe

ThisWorkbook bezieht sich auf die Arbeitsmappe, in der der Code ausgeführt wird.

Jede Arbeitsmappe hätte ein ThisWorkbook-Objekt als Teil (sichtbar im Projekt-Explorer).

‘ThisWorkbook’ kann reguläre Makros (ähnlich denen, die wir mit Modulen hinzufügen) sowie Ereignisprozeduren speichern. Eine Ereignisprozedur ist etwas, das basierend auf einem Ereignis ausgelöst wird – wie z. B. ein Doppelklick auf eine Zelle, das Speichern einer Arbeitsmappe oder das Aktivieren eines Arbeitsblatts.

Jede Ereignisprozedur, die Sie in dieser „DieseArbeitsmappe“ speichern, wäre in der gesamten Arbeitsmappe verfügbar, im Gegensatz zu den Ereignissen auf Blattebene, die nur auf die spezifischen Blätter beschränkt sind.

Wenn Sie beispielsweise im Projekt-Explorer auf das Objekt ThisWorkbook doppelklicken und den folgenden Code kopieren und einfügen, wird die Zellenadresse jedes Mal angezeigt, wenn Sie auf eine der Zellen in der gesamten Arbeitsmappe doppelklicken.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) MsgBox Target.Address End Sub

Während die Hauptaufgabe von ThisWorkbook darin besteht, Ereignisprozeduren zu speichern, können Sie damit auch auf die Arbeitsmappe verweisen, in der der Code ausgeführt wird.

Der folgende Code würde den Namen der Arbeitsmappe zurückgeben, in der der Code ausgeführt wird.

Sub ThisWorkbookName() MsgBox ThisWorkbook.Name End Sub

Der Vorteil der Verwendung von ThisWorkbook (über ActiveWorkbook) besteht darin, dass es in allen Fällen auf dieselbe Arbeitsmappe (diejenige, die den Code enthält) verweisen würde. Wenn Sie also einen VBA-Code verwenden, um eine neue Arbeitsmappe hinzuzufügen, würde sich die ActiveWorkbook ändern, aber ThisWorkbook würde weiterhin auf diejenige verweisen, die den Code enthält.

Erstellen eines neuen Arbeitsmappenobjekts

Der folgende Code erstellt eine neue Arbeitsmappe.

Unter CreateNewWorkbook() Workbooks.Add End Sub

Wenn Sie eine neue Arbeitsmappe hinzufügen, wird sie zur aktiven Arbeitsmappe.

Der folgende Code fügt eine neue Arbeitsmappe hinzu und zeigt Ihnen dann den Namen dieser Arbeitsmappe (dies wäre der Standardname des Book1-Typs).

Sub CreateNewWorkbook() Workbooks.Add MsgBox ActiveWorkbook.Name End Sub

Öffnen Sie eine Arbeitsmappe mit VBA

Sie können VBA verwenden, um eine bestimmte Arbeitsmappe zu öffnen, wenn Sie den Dateipfad der Arbeitsmappe kennen.

Der folgende Code öffnet die Arbeitsmappe - Example.xlsx, die sich im Ordner "Dokumente" auf meinem System befindet.

Sub OpenWorkbook() Workbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx") End Sub

Falls die Datei im Standardordner vorhanden ist, in dem VBA standardmäßig neue Dateien speichert, können Sie einfach den Arbeitsmappennamen angeben - ohne den gesamten Pfad.

Sub OpenWorkbook() Workbooks.Open ("Examples.xlsx") End Sub

Falls die Arbeitsmappe, die Sie öffnen möchten, nicht vorhanden ist, wird ein Fehler angezeigt.

Um diesen Fehler zu vermeiden, können Sie Ihrem Code einige Zeilen hinzufügen, um zuerst zu überprüfen, ob die Datei existiert oder nicht, und wenn sie existiert, versuchen Sie dann, sie zu öffnen.

Der folgende Code würde den Dateispeicherort überprüfen und wenn er nicht existiert, wird eine benutzerdefinierte Nachricht angezeigt (nicht die Fehlermeldung):

Sub OpenWorkbook() If Dir("C:\Users\sumit\Documents\Examples.xlsx") "" Then Workbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx") Else MsgBox "Die Datei funktioniert nicht 'nicht vorhanden" End If End Sub

Sie können auch das Dialogfeld Öffnen verwenden, um die Datei auszuwählen, die Sie öffnen möchten.

Sub OpenWorkbook() Bei Fehler Resume Next Dim FilePath As String FilePath = Application.GetOpenFilename Workbooks.Open (FilePath) End Sub

Der obige Code öffnet das Dialogfeld Öffnen. Wenn Sie eine Datei auswählen, die Sie öffnen möchten, wird der Dateipfad der FilePath-Variablen zugewiesen. Workbooks.Open verwendet dann den Dateipfad, um die Datei zu öffnen.

Falls der Benutzer keine Datei öffnet und auf die Schaltfläche Abbrechen klickt, wird FilePath zu False. Um in diesem Fall einen Fehler zu vermeiden, haben wir die Anweisung „On Error Resume Next“ verwendet.

Verwandt: Erfahren Sie alles über die Fehlerbehandlung in Excel VBA

Speichern einer Arbeitsmappe

Verwenden Sie den folgenden Code, um die aktive Arbeitsmappe zu speichern:

Sub SaveWorkbook() ActiveWorkbook.Save End Sub

Dieser Code funktioniert für die Arbeitsmappen, die bereits zuvor gespeichert wurden. Da die Arbeitsmappe das obige Makro enthält, geht das Makro beim nächsten Öffnen verloren, wenn es nicht als .xlsm- (oder .xls-)Datei gespeichert wurde.

Wenn Sie die Arbeitsmappe zum ersten Mal speichern, wird eine Eingabeaufforderung wie unten gezeigt angezeigt:

Beim ersten Speichern ist es besser, die Option „Speichern“ zu verwenden.

Der folgende Code würde die aktive Arbeitsmappe als .xlsm-Datei am Standardspeicherort (das ist der Dokumentordner in meinem System) speichern.

Sub SaveWorkbook() ActiveWorkbook.SaveAs Filename:="Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled End Sub

Wenn Sie möchten, dass die Datei an einem bestimmten Ort gespeichert wird, müssen Sie dies im Wert für Dateiname angeben. Der folgende Code speichert die Datei auf meinem Desktop.

Sub SaveWorkbook() ActiveWorkbook.SaveAs Filename:="C:\Users\sumit\Desktop\Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled End Sub

Wenn Sie möchten, dass der Benutzer die Möglichkeit hat, den Speicherort für die Datei auszuwählen, können Sie das Dialogfeld Saveas aufrufen. Der folgende Code zeigt das Dialogfeld Speichern unter und ermöglicht dem Benutzer, den Speicherort auszuwählen, an dem die Datei gespeichert werden soll.

Sub SaveWorkbook() Dim FilePath As String FilePath = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Filename:=FilePath & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled End Sub

Beachten Sie, dass Sie statt FileFormat:=xlOpenXMLWorkbookMacroEnabled auch FileFormat:=52 verwenden können, wobei 52 der Code xlOpenXMLWorkbookMacroEnabled ist.

Alle geöffneten Arbeitsmappen speichern

Wenn Sie mehr als eine Arbeitsmappe geöffnet haben und alle Arbeitsmappen speichern möchten, können Sie den folgenden Code verwenden:

Sub SaveAllWorkbooks() Dim wb As Workbook For Each wb In Workbooks wb.Save Next wb End Sub

Das Obige speichert alle Arbeitsmappen, einschließlich derer, die noch nie gespeichert wurden. Die Arbeitsmappen, die zuvor nicht gespeichert wurden, werden am Standardspeicherort gespeichert.

Wenn Sie nur die zuvor gespeicherten Arbeitsmappen speichern möchten, können Sie den folgenden Code verwenden:

Sub SaveAllWorkbooks() Dim wb As Workbook For Every wb In Workbooks If wb.Path "" Then wb.Save End If Next wb End Sub

Speichern und Schließen aller Arbeitsmappen

Wenn Sie alle Arbeitsmappen schließen möchten, mit Ausnahme der Arbeitsmappe, die den aktuellen Code enthält, können Sie den folgenden Code verwenden:

Sub CloseandSaveWorkbooks() Dim wb As Workbook For Every wb In Workbooks If wb.Name ThisWorkbook.Name Then wb.Close SaveChanges:=True End If Next wb End Sub

Der obige Code würde alle Arbeitsmappen schließen (außer der Arbeitsmappe, die den Code enthält - ThisWorkbook). Falls es Änderungen in diesen Arbeitsmappen gibt, werden die Änderungen gespeichert. Falls es eine Arbeitsmappe gibt, die noch nie gespeichert wurde, wird das Dialogfeld Speichern unter angezeigt.

Speichern einer Kopie der Arbeitsmappe (mit Zeitstempel)

Wenn ich mit komplexen Daten und Dashboards in Excel-Arbeitsmappen arbeite, erstelle ich oft verschiedene Versionen meiner Arbeitsmappen. Dies ist hilfreich, falls mit meiner aktuellen Arbeitsmappe etwas schief geht. Ich würde zumindest eine Kopie davon unter einem anderen Namen speichern lassen (und ich würde nur die Arbeit verlieren, die ich nach dem Erstellen einer Kopie gemacht habe).

Hier ist der VBA-Code, der eine Kopie Ihrer Arbeitsmappe erstellt und am angegebenen Ort speichert.

Sub CreateaCopyofWorkbook() ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy.xlsm" End Sub

Der obige Code würde jedes Mal, wenn Sie dieses Makro ausführen, eine Kopie Ihrer Arbeitsmappe speichern.

Obwohl dies großartig funktioniert, würde ich mich wohler fühlen, wenn ich verschiedene Kopien gespeichert hätte, wenn ich diesen Code ausführe. Der Grund dafür ist, dass, wenn ich einen versehentlichen Fehler mache und dieses Makro ausführe, die Arbeit mit den Fehlern gespeichert wird. Und ich hätte keinen Zugriff auf die Arbeit, bevor ich den Fehler gemacht habe.

Um solche Situationen zu bewältigen, können Sie den folgenden Code verwenden, der jedes Mal eine neue Kopie der Arbeit speichert, wenn Sie sie speichern. Und es fügt auch ein Datum und einen Zeitstempel als Teil des Arbeitsmappennamens hinzu. Auf diese Weise können Sie jeden Fehler, den Sie begangen haben, nachverfolgen, da Sie nie zuvor erstellte Backups verlieren.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy" & Format(Now(), "dd-mm-yy-hh-mm-ss -AMPM") & ".xlsm" End Sub

Der obige Code würde jedes Mal, wenn Sie dieses Makro ausführen, eine Kopie erstellen und dem Arbeitsmappennamen einen Datums-/Zeitstempel hinzufügen.

Erstellen Sie eine neue Arbeitsmappe für jedes Arbeitsblatt

In einigen Fällen verfügen Sie möglicherweise über eine Arbeitsmappe mit mehreren Arbeitsblättern, und Sie möchten für jedes Arbeitsblatt eine Arbeitsmappe erstellen.

Dies kann der Fall sein, wenn Sie Monats-/Quartalsberichte in einer einzelnen Arbeitsmappe haben und diese für jedes Arbeitsblatt in eine Arbeitsmappe aufteilen möchten.

Oder wenn Sie abteilungsbezogene Berichte haben und diese in einzelne Arbeitsmappen aufteilen möchten, damit Sie diese einzelnen Arbeitsmappen an die Abteilungsleiter senden können.

Hier ist der Code, der für jedes Arbeitsblatt eine Arbeitsmappe erstellt, ihr den gleichen Namen wie dem des Arbeitsblatts gibt und sie im angegebenen Ordner speichert.

Sub CreateWorkbookforWorksheets() Dim ws As Worksheet Dim wb As Workbook For Each ws In ThisWorkbook.Worksheets Set wb = Workbooks.Add ws.Copy Before:=wb.Sheets(1) Application.DisplayAlerts = False wb.Sheets(2).Delete Application.DisplayAlerts = True wb.SaveAs "C:\Users\sumit\Desktop\Test\" & ws.Name & ".xlsx" wb.Close Next ws End Sub

Im obigen Code haben wir zwei Variablen ‚ws‘ und ‚wb‘ verwendet.

Der Code durchläuft jedes Arbeitsblatt (mithilfe der For Each Next-Schleife) und erstellt eine Arbeitsmappe dafür. Es verwendet auch die Kopiermethode des Arbeitsblattobjekts, um eine Kopie des Arbeitsblatts in der neuen Arbeitsmappe zu erstellen.

Beachten Sie, dass ich die SET-Anweisung verwendet habe, um die Variable 'wb' jeder neuen Arbeitsmappe zuzuweisen, die vom Code erstellt wird.

Mit dieser Technik können Sie einer Variablen ein Arbeitsmappenobjekt zuweisen. Dies wird im nächsten Abschnitt behandelt.

Arbeitsmappenobjekt einer Variablen zuweisen

In VBA können Sie einer Variablen ein Objekt zuweisen und dann die Variable verwenden, um auf dieses Objekt zu verweisen.

Im folgenden Code verwende ich beispielsweise VBA, um eine neue Arbeitsmappe hinzuzufügen und diese Arbeitsmappe dann der Variablen wb zuzuweisen. Dazu muss ich die SET-Anweisung verwenden.

Nachdem ich der Variablen die Arbeitsmappe zugewiesen habe, werden auch der Variablen alle Eigenschaften der Arbeitsmappe zur Verfügung gestellt.

Sub AssigntoVariable() Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs Filename:="C:\Users\sumit\Desktop\Examples.xlsx" End Sub

Beachten Sie, dass der erste Schritt im Code darin besteht, „wb“ als Variable vom Typ Arbeitsmappe zu deklarieren. Dies teilt VBA mit, dass diese Variable das Arbeitsmappenobjekt enthalten kann.

Die nächste Anweisung verwendet SET, um die Variable der neuen Arbeitsmappe zuzuweisen, die wir hinzufügen. Sobald diese Zuweisung abgeschlossen ist, können wir die wb-Variable verwenden, um die Arbeitsmappe zu speichern (oder etwas anderes damit zu tun).

Durchlaufen geöffneter Arbeitsmappen

Wir haben oben bereits einige Beispielcodes gesehen, die Schleifen im Code verwendet haben.

In diesem Abschnitt werde ich verschiedene Möglichkeiten zum Durchlaufen geöffneter Arbeitsmappen mit VBA erklären.

Angenommen, Sie möchten alle geöffneten Arbeitsmappen speichern und schließen, mit Ausnahme derjenigen mit dem darin enthaltenen Code, dann können Sie den folgenden Code verwenden:

Sub CloseandSaveWorkbooks() Dim wb As Workbook For Every wb In Workbooks If wb.Name ThisWorkbook.Name Then wb.Close SaveChanges:=True End If Next wb End Sub

Der obige Code verwendet die For Each-Schleife, um jede Arbeitsmappe in der Workbooks-Auflistung zu durchlaufen. Dazu müssen wir zunächst ‚wb‘ als Variable vom Arbeitsmappentyp deklarieren.

In jedem Schleifenzyklus wird jeder Arbeitsmappenname analysiert und wenn er nicht mit dem Namen der Arbeitsmappe übereinstimmt, die den Code enthält, wird sie nach dem Speichern ihres Inhalts geschlossen.

Das gleiche kann auch mit einer anderen Schleife erreicht werden, wie unten gezeigt:

Sub CloseWorkbooks() Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks(i).Name ThisWorkbook.Name Then Workbooks(i).Close SaveChanges:=True End If Next i End Sub

Der obige Code verwendet die For Next-Schleife, um alle Arbeitsmappen mit Ausnahme derjenigen zu schließen, die den Code enthält. In diesem Fall müssen wir keine Arbeitsmappenvariable deklarieren, sondern müssen stattdessen die Gesamtzahl der geöffneten Arbeitsmappen zählen. Wenn wir die Anzahl haben, verwenden wir die For Next-Schleife, um jede Arbeitsmappe durchzugehen. Außerdem verwenden wir in diesem Fall die Indexnummer, um auf die Arbeitsmappen zu verweisen.

Beachten Sie, dass wir im obigen Code mit Schritt -1 von WbCount zu 1 schleifen. Dies wird benötigt, da bei jeder Schleife die Arbeitsmappe geschlossen und die Anzahl der Arbeitsmappen um 1 verringert wird.

Fehler beim Arbeiten mit dem Arbeitsmappenobjekt (Laufzeitfehler ‘9’)

Einer der häufigsten Fehler, die bei der Arbeit mit Arbeitsmappen auftreten können, ist - Laufzeitfehler "9" - Tiefgestellter Wert außerhalb des zulässigen Bereichs.

Im Allgemeinen sind VBA-Fehler nicht sehr informativ und überlassen es oft Ihnen, herauszufinden, was schief gelaufen ist.

Hier sind einige der möglichen Gründe, die zu diesem Fehler führen können:

• Die Arbeitsmappe, auf die Sie zugreifen möchten, ist nicht vorhanden. Wenn ich beispielsweise versuche, mit Workbooks(5) auf die fünfte Arbeitsmappe zuzugreifen, und nur 4 Arbeitsmappen geöffnet sind, erhalte ich diese Fehlermeldung.
• Wenn Sie einen falschen Namen verwenden, um auf die Arbeitsmappe zu verweisen. Wenn Ihr Arbeitsmappenname beispielsweise "Examples.xlsx" lautet und Sie "Example.xlsx" verwenden. dann wird Ihnen dieser Fehler angezeigt.
• Wenn Sie keine Arbeitsmappe gespeichert haben und die Erweiterung verwenden, wird dieser Fehler angezeigt. Wenn Ihr Arbeitsmappenname beispielsweise Book1 lautet und Sie den Namen Book1.xlsx verwenden, ohne ihn zu speichern, wird dieser Fehler angezeigt.
• Die Arbeitsmappe, auf die Sie zugreifen möchten, ist geschlossen.

Erhalten Sie eine Liste aller geöffneten Arbeitsmappen

Wenn Sie eine Liste aller geöffneten Arbeitsmappen in der aktuellen Arbeitsmappe (der Arbeitsmappe, in der Sie den Code ausführen) abrufen möchten, können Sie den folgenden Code verwenden:

Sub GetWorkbookNames() Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range("A1").Activate For i = 1 To wbcount Range("A1").Offset(i - 1, 0). Wert = Arbeitsmappen(i).Name Weiter i End Sub

Der obige Code fügt ein neues Arbeitsblatt hinzu und listet dann die Namen aller geöffneten Arbeitsmappen auf.

Wenn Sie auch ihren Dateipfad erhalten möchten, können Sie den folgenden Code verwenden:

Sub GetWorkbookNames() Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range("A1").Activate For i = 1 To wbcount Range("A1").Offset(i - 1, 0). Wert = Arbeitsmappen(i).Pfad & "\" & Arbeitsmappen(i).Name Weiter i End Sub

Öffnen Sie die angegebene Arbeitsmappe, indem Sie auf die Zelle doppelklicken

Wenn Sie eine Liste von Dateipfaden für Excel-Arbeitsmappen haben, können Sie den folgenden Code verwenden, um einfach auf die Zelle mit dem Dateipfad zu doppelklicken, und diese Arbeitsmappe wird geöffnet.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Workbooks.Open Target.Value End Sub

Dieser Code wird im Codefenster von ThisWorkbook platziert.

Um dies zu tun:

• Doppelklicken Sie im Projektexplorer auf das Objekt ThisWorkbook. Beachten Sie, dass sich das ThisWorkbook-Objekt in der Arbeitsmappe befinden sollte, in der Sie diese Funktionalität verwenden möchten.
• Kopieren Sie den obigen Code und fügen Sie ihn ein.

Wenn Sie nun den genauen Pfad der Dateien haben, die Sie öffnen möchten, können Sie dies tun, indem Sie einfach auf den Dateipfad doppelklicken und VBA würde diese Arbeitsmappe sofort öffnen.

Wo soll der VBA-Code abgelegt werden?

Sie fragen sich, wo der VBA-Code in Ihrer Excel-Arbeitsmappe hingehört?

Excel hat ein VBA-Backend namens VBA-Editor. Sie müssen den Code kopieren und in das Codefenster des VB-Editor-Moduls einfügen.

Hier sind die Schritte, um dies zu tun:

1. Wechseln Sie zur Registerkarte Entwickler.
2. Klicken Sie auf die Visual Basic-Option. Dies öffnet den VB-Editor im Backend.
3. Klicken Sie im Bereich Projekt-Explorer im VB-Editor mit der rechten Maustaste auf ein beliebiges Objekt für die Arbeitsmappe, in die Sie den Code einfügen möchten. Wenn Sie den Projekt-Explorer nicht sehen, gehen Sie zur Registerkarte Ansicht und klicken Sie auf Projekt-Explorer.
4. Gehen Sie zu Einfügen und klicken Sie auf Modul. Dadurch wird ein Modulobjekt für Ihre Arbeitsmappe eingefügt.
5. Kopieren Sie den Code und fügen Sie ihn in das Modulfenster ein.

Möglicherweise gefallen Ihnen auch die folgenden Excel VBA-Tutorials:

• So zeichnen Sie ein Makro in Excel auf.
• Erstellen einer benutzerdefinierten Funktion in Excel.
• So erstellen und verwenden Sie ein Add-In in Excel.
• So setzen Sie Makros fort, indem Sie sie in die persönliche Makroarbeitsmappe einfügen.
• Rufen Sie die Liste der Dateinamen aus einem Ordner in Excel (mit und ohne VBA) ab.
• So verwenden Sie die Excel VBA InStr-Funktion (mit praktischen BEISPIELEN).
• So sortieren Sie Daten in Excel mit VBA (eine Schritt-für-Schritt-Anleitung).