Daten aus geschlossener Arbeitsmappe per VBA auslesen

Eine Auswertung soll Werte aus einer anderen Excel-Datei holen, etwa die Umsätze aus der Monatsdatei des Vertriebs. Die Datei zu öffnen, zu kopieren und wieder zu schließen ist mühsam. Per VBA geht es auf Knopfdruck, für einzelne Zellen sogar ganz ohne Öffnen der Quelle.

Die Kurzlösung

Diese Funktion liest eine einzelne Zelle aus einer geschlossenen Arbeitsmappe. Fügen Sie sie im VBA-Editor (Alt+F11) über Einfügen, Modul ein:

Option Explicit

' Liest einen Wert aus einer geschlossenen Arbeitsmappe, ohne sie zu öffnen.
' Beispiel: WertAusGeschlossenerMappe("C:\Daten", "Umsatz.xlsx", "Januar", "B5")
Public Function WertAusGeschlossenerMappe(ByVal pfad As String, ByVal datei As String, _
                                          ByVal blatt As String, ByVal zelle As String) As Variant
    Dim bezug As String

    If Right$(pfad, 1) <> Application.PathSeparator Then pfad = pfad & Application.PathSeparator
    If Len(Dir$(pfad & datei)) = 0 Then
        WertAusGeschlossenerMappe = CVErr(xlErrRef)        ' Datei fehlt: #BEZUG!
        Exit Function
    End If

    ' Bezug wie in einer Formel: 'C:\Daten\[Umsatz.xlsx]Januar'!R5C2
    ' Apostrophe im Datei- oder Blattnamen werden verdoppelt.
    bezug = "'" & Replace(pfad & "[" & datei & "]" & blatt, "'", "''") & "'!" & _
            ThisWorkbook.Worksheets(1).Range(zelle).Address(ReferenceStyle:=xlR1C1)
    WertAusGeschlossenerMappe = Application.ExecuteExcel4Macro(bezug)
End Function

Aufgerufen wird sie aus einem Makro, etwa so:

Public Sub UmsatzHolen()
    ThisWorkbook.Worksheets("Auswertung").Range("B2").Value = _
        WertAusGeschlossenerMappe("C:\Daten", "Umsatz.xlsx", "Januar", "B5")
End Sub

So arbeitet die Funktion

ExecuteExcel4Macro wertet einen Ausdruck in der alten Makrosprache von Excel aus. Ein externer Bezug wie 'C:\Daten\[Umsatz.xlsx]Januar'!R5C2 liefert dabei den Wert der Zelle, auch wenn die Datei geschlossen ist. Die Funktion setzt diesen Bezug zusammen:

  • Der Pfad bekommt bei Bedarf einen abschließenden Backslash.
  • Fehlt die Datei, liefert die Funktion den Fehlerwert #BEZUG!, statt abzubrechen.
  • Die Zelladresse wird in die Schreibweise Z1S1 (R1C1) umgewandelt, die ExecuteExcel4Macro erwartet.
  • Ein Apostroph im Datei- oder Blattnamen, etwa „Max’s Daten“, wird verdoppelt.

Die Funktion liefert Text, Zahlen und Formelergebnisse. Eine leere Zelle ergibt 0, ein Fehler in der Quelle den Fehlerwert.

Ganze Bereiche: kurz öffnen ist schneller

Jeder Aufruf von ExecuteExcel4Macro liest die Datei erneut, bei vielen Zellen summiert sich das spürbar. Wird die Mappe dagegen einmal geöffnet, kommt der ganze Bereich in einem Schritt. Für Bereiche ist es deshalb besser, die Mappe unsichtbar und schreibgeschützt zu öffnen, die Werte zu holen und sie gleich wieder zu schließen:

' Liest einen ganzen Bereich: Mappe schreibgeschützt öffnen, Werte holen, wieder schließen.
' Ist genau diese Datei schon offen, wird sie verwendet und bleibt offen.
' Makros der Quelle werden beim Öffnen nicht ausgeführt.
Public Function BereichAusMappe(ByVal dateiPfad As String, ByVal blatt As String, _
                                ByVal adresse As String) As Variant
    Dim wb As Workbook, offen As Workbook
    Dim warOffen As Boolean, aktualisieren As Boolean
    Dim sicherheit As MsoAutomationSecurity
    Dim nummer As Long, beschreibung As String

    aktualisieren = Application.ScreenUpdating
    sicherheit = Application.AutomationSecurity
    On Error GoTo Fehler

    For Each offen In Workbooks                     ' schon offen? nur bei gleichem vollständigem Pfad
        If StrComp(offen.FullName, dateiPfad, vbTextCompare) = 0 Then
            Set wb = offen
            Exit For
        ElseIf StrComp(offen.Name, Dir$(dateiPfad), vbTextCompare) = 0 Then
            Err.Raise vbObjectError + 1, , "Eine andere Datei mit dem Namen " & offen.Name & " ist bereits geöffnet."
        End If
    Next offen
    warOffen = Not wb Is Nothing

    Application.ScreenUpdating = False
    If Not warOffen Then
        Application.AutomationSecurity = msoAutomationSecurityForceDisable
        Set wb = Workbooks.Open(Filename:=dateiPfad, ReadOnly:=True, UpdateLinks:=0, AddToMru:=False)
        Application.AutomationSecurity = sicherheit
    End If
    BereichAusMappe = wb.Worksheets(blatt).Range(adresse).Value
    If Not warOffen Then wb.Close SaveChanges:=False
    Application.ScreenUpdating = aktualisieren
    Exit Function

Fehler:
    nummer = Err.Number
    beschreibung = Err.Description
    Application.AutomationSecurity = sicherheit
    If Not warOffen And Not wb Is Nothing Then wb.Close SaveChanges:=False
    Application.ScreenUpdating = aktualisieren
    Err.Raise nummer, , beschreibung
End Function

Das Ergebnis ist ein Datenfeld, das Sie in einem Schritt in einen gleich großen Bereich schreiben:

ThisWorkbook.Worksheets("Auswertung").Range("B2:D13").Value = _
    BereichAusMappe("C:\Daten\Umsatz.xlsx", "Januar", "A1:C12")

Die Prüfung warOffen ist wichtig: Hat der Anwender die Quelle gerade selbst geöffnet, darf das Makro sie nicht schließen. Verglichen wird dabei der vollständige Pfad. Ist nur eine gleichnamige Datei aus einem anderen Ordner offen, meldet die Funktion das, statt Werte aus der falschen Mappe zu liefern. Excel kann zwei Mappen mit gleichem Namen nicht gleichzeitig öffnen.

AutomationSecurity sorgt dafür, dass Makros der Quelldatei beim Öffnen nicht laufen, etwa ein Workbook_Open, das Meldungen zeigt oder Daten ändert. Der Code stellt die Einstellung danach wieder zurück, auch im Fehlerfall.

Ohne Makro: Formelbezug und Power Query

WegWann geeignet
Formel ='C:\Daten\[Umsatz.xlsx]Januar'!B5einzelne Werte, die sich bei jeder Aktualisierung neu holen sollen, ganz ohne VBA
ExecuteExcel4Macroeinzelne Werte per Makro, Quelle bleibt geschlossen
Workbooks.Open schreibgeschütztganze Bereiche, deutlich schneller
Power Query (Daten, Daten abrufen, Aus Datei)regelmäßiger Import ganzer Tabellen, auch aus vielen Dateien eines Ordners

Ein Formelbezug auf eine geschlossene Datei funktioniert ebenfalls. Excel fragt beim Öffnen der Mappe allerdings, ob die Verknüpfungen aktualisiert werden sollen.

Typische Fehler

  • Blatt gibt es nicht: Stimmt der Blattname nicht, öffnet ExecuteExcel4Macro ein Auswahlfenster für das Blatt und wartet auf den Anwender. Prüfen Sie den Namen deshalb genau.
  • Datei in OneDrive oder SharePoint: Liefert ThisWorkbook.Path eine Internetadresse (https://…), findet Dir die Datei nicht. Verwenden Sie einen lokalen oder synchronisierten Ordnerpfad.
  • Variable überschreibt die Zelle: In älteren Fassungen dieses Codes stand in der Schleife zelle = zelle.Address(False, False). Weil zelle eine Zelle ist, schreibt diese Zeile die Adresse in die Zelle und überschreibt den Inhalt. Verwenden Sie für die Adresse eine eigene Textvariable.
  • Leere Zellen werden 0: ExecuteExcel4Macro und Formelbezüge liefern für eine leere Zelle 0. Soll eine leere Zelle leer bleiben, prüfen Sie das Ergebnis oder holen Sie Bereiche mit Workbooks.Open.

Beispieldateien

Das ZIP-Archiv enthält die Mappe mit dem Code und zwei Schaltflächen sowie eine Quelldatei mit Monatsumsätzen. Entpacken Sie beide Dateien in denselben Ordner und lassen Sie die Quelle geschlossen. Weil die Mappe Makros enthält, gibt Windows sie nach dem Download eventuell erst nach einer Freigabe frei, siehe Makros blockiert.

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.