VBA: Inhaltsverzeichnis, Dateiliste und Makros anderer Mappen

Drei kleinere Aufgaben, die in größeren Excel-Lösungen immer wieder vorkommen. Den Code fügen Sie über Alt+F11 > Einfügen > Modul ein.

Inhaltsverzeichnis aller Blätter

In Mappen mit vielen Blättern hilft eine Übersicht am Anfang, in der jeder Blattname ein Link ist:

Option Explicit

' Legt am Anfang der Mappe ein Blatt "Inhalt" an (oder leert es) und listet alle Blätter
' als anklickbare Links auf.
Public Sub InhaltsverzeichnisAnlegen()
    Dim inhalt As Worksheet, blatt As Worksheet, zeile As Long

    On Error Resume Next
    Set inhalt = ThisWorkbook.Worksheets("Inhalt")
    On Error GoTo 0
    If inhalt Is Nothing Then
        Set inhalt = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
        inhalt.Name = "Inhalt"
    End If
    inhalt.Cells.Clear
    inhalt.Range("A1").Value = "Inhalt"
    inhalt.Range("A1").Font.Bold = True

    zeile = 3
    For Each blatt In ThisWorkbook.Worksheets
        If Not blatt Is inhalt And blatt.Visible = xlSheetVisible Then
            inhalt.Hyperlinks.Add Anchor:=inhalt.Cells(zeile, 1), Address:="", _
                SubAddress:="'" & Replace(blatt.Name, "'", "''") & "'!A1", TextToDisplay:=blatt.Name
            zeile = zeile + 1
        End If
    Next blatt
    inhalt.Columns(1).AutoFit
End Sub

Das Makro legt das Blatt „Inhalt“ vorne an oder leert es, wenn es schon existiert. Ausgeblendete Blätter erscheinen nicht. Bei den Blättern „Januar“, „Umsatz 2026“, „Peter’s Liste“ und einem ausgeblendeten Blatt entstehen drei Links. Auch ein zweiter Lauf erzeugt kein weiteres Inhaltsblatt.

Blattnamen mit Leerzeichen oder Apostroph brauchen in Links Hochkommas, ein Apostroph im Namen wird verdoppelt. Ohne das führt der Link bei „Peter’s Liste“ ins Leere.

Ältere Fassungen schreiben die Namen nur als Text in das gerade aktive Blatt. Läuft das Makro auf dem falschen Blatt, werden dort Werte überschrieben.

Dateien eines Ordners auflisten

Für eine Übersicht über Belege, Berichte oder Vorlagen schreibt die Funktion alle Dateien eines Ordners mit Größe und Änderungsdatum in ein Blatt. Jeder Dateiname ist ein Link, der die Datei öffnet:

Option Explicit

' Schreibt alle Dateien eines Ordners mit Größe und Änderungsdatum ab Zeile 2 in ein Blatt.
' muster schränkt ein, etwa "*.xlsx". Liefert die Anzahl der Dateien.
Public Function DateienAuflisten(ByVal ordner As String, ByVal ziel As Worksheet, Optional ByVal muster As String = "*.*") As Long
    Dim datei As String, zeile As Long

    If Right$(ordner, 1) <> "\" Then ordner = ordner & "\"
    If Len(Dir$(ordner, vbDirectory)) = 0 Then Exit Function

    ziel.Cells.Clear
    ziel.Range("A1:C1").Value = Array("Datei", "Größe (KB)", "Geändert")
    zeile = 2
    datei = Dir$(ordner & muster)
    Do While Len(datei) > 0
        ziel.Hyperlinks.Add Anchor:=ziel.Cells(zeile, 1), Address:=ordner & datei, TextToDisplay:=datei
        ziel.Cells(zeile, 2).Value = Round(FileLen(ordner & datei) / 1024, 1)
        ziel.Cells(zeile, 3).Value = FileDateTime(ordner & datei)
        zeile = zeile + 1
        datei = Dir$
    Loop
    ziel.Columns("C").NumberFormat = "dd.mm.yyyy hh:mm"
    ziel.Columns("A:C").AutoFit
    DateienAuflisten = zeile - 2
End Function

Aufruf: DateienAuflisten "\\Server\Belege\2026", Worksheets("Liste"), "*.pdf". Ohne Muster erscheinen alle Dateien. Gibt es den Ordner nicht, liefert die Funktion 0 und lässt das Blatt unverändert.

Unterordner werden nicht durchsucht. Dafür braucht es eine Funktion, die sich für jeden Unterordner selbst aufruft, oder Power Query: Daten > Daten abrufen > Aus Datei > Aus Ordner liefert alle Dateien samt Unterordnern als Tabelle, ganz ohne Makro, und lässt sich per Klick aktualisieren.

Makro aus einer anderen Mappe aufrufen

Häufig gebrauchte Makros liegen in einer zentralen Werkzeugmappe. Andere Mappen rufen sie mit Application.Run auf:

Application.Run "'Werkzeuge 2026.xlsm'!Hallo"                     ' Prozedur ohne Rückgabe
ergebnis = Application.Run("'Werkzeuge 2026.xlsm'!Verdoppeln", 21) ' Funktion mit Argument, ergibt 42

Der Mappenname steht in Hochkommas, sobald er Leerzeichen enthält. Argumente folgen durch Komma getrennt, Rückgabewerte von Funktionen nimmt Application.Run entgegen.

Die Werkzeugmappe muss geöffnet sein. Ist sie geschlossen, bricht der Aufruf mit Fehler 1004 ab. Öffnen Sie sie deshalb vorher, bei Bedarf unsichtbar:

Dim werkzeuge As Workbook
Set werkzeuge = Workbooks.Open("\\Server\Vorlagen\Werkzeuge 2026.xlsm", ReadOnly:=True)
Application.Run "'" & werkzeuge.Name & "'!Hallo"
werkzeuge.Close SaveChanges:=False

Für Makros, die in jeder Mappe zur Verfügung stehen sollen, ist ein Add-In (.xlam) oder die persönliche Makroarbeitsmappe die bessere Lösung.

Passende Beiträge

Lieber machen lassen?

Wir programmieren die Lösung für Ihre Datei.

Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.