Daten aus geschlossener Arbeitsmappe über Zellverknüpfungen auslesen

Variante 1: Verwendung der Formeln zum Auslesen der Werte

In dieser ersten Variante werden die Daten aus der geschlossenen Arbeitsmappe über Formelbezüge ausgelesen und entsprechend in der Zieldatei dargestellt. Die Formeln werden dabei direkt per VBA erzeugt.
Dieser Ansatz bietet den Vorteil, dass die Werte in der Zieldatei automatisch aktualisiert werden, wenn sich die Daten in der Quelldatei ändern. 

Hier der entsprechende VBA-Code, welcher in einem Code-Modul zu erfassen ist:

Const strSheetQ As String = "Tabelle1" '** Diese Tabelle wird ausgelesen"
Const strSheetZ As String = "Tabelle1" '** Die Tabelle in dieser Datei, in welche die Daten übertragen werden
Const strRange As String = "A1:A4" '** Der Bereich wird ausgelesen
Const strFile As String = "F:\Excel\Beispiele\geschlossene Mappe2.xlsx" '** Pfad und Datei

Public Sub DatenHolen()
  '** Daten aus geschlossener Datei per Zell-Verknüpfung auslesen und Formel zur externen Datei erzeugen
  With ThisWorkbook.Worksheets(strSheetZ)
    '** Eintragen der Formel
    .Range(strRange).Formula = "='" & Mid(strFile, 1, InStrRev(strFile, "\")) & "[" & _
    Mid(strFile, InStrRev(strFile, "\") + 1) & "]" & strSheetQ & "'!" & strRange
  End With

End Sub

Variante 2: Umwandeln der Formel-Inhalte in unveränderliche Werte

In der zweiten Variante wird genau der gleiche Ansatz verwendet, nur werden die Formeln in einem zweiten Schritt in fixe Werte umgewandelt. Dies hat zur Folge, dass sich Änderungen in der Quelldatei nicht automatisch in der Zieldatei auswirken. Eine Aktualisierung der Werte in der Zieldatei wird erst durchgeführt, wenn der untenstehende VBA-Code erneut ausgeführt wird.

Dieser Code ist absolut identisch mit dem Code aus Variante 1, nur dass eine VBA-Zeile zusätzlich vorhanden ist, welche die über die eingefügten Formel ausgelesene Daten in fixe Werte umwandelt. 

Const strSheetQ As String = "Tabelle1" '** Diese Tabelle wird ausgelesen"
Const strSheetZ As String = "Tabelle1" '** Die Tabelle in dieser Datei, in welche die Daten übertragen werden
Const strRange As String = "A1:A4" '** Der Bereich wird ausgelesen
Const strFile As String = "F:\Excel\Beispiele\geschlossene Mappe2.xlsx" '** Pfad und Datei

Public Sub DatenHolen()
  '** Daten aus geschlossener Datei per Zell-Verknüpfung auslesen und Formel zur externen Datei erzeugen
  With ThisWorkbook.Worksheets(strSheetZ)
    '** Eintragen der Formel
    .Range(strRange).Formula = "='" & Mid(strFile, 1, InStrRev(strFile, "\")) & "[" & _
    Mid(strFile, InStrRev(strFile, "\") + 1) & "]" & strSheetQ & "'!" & strRange
  End With

  '** Umwandeln der Formel in fixe Werte
  .Range(strRange).Value = .Range(strRange).Value

End Sub