Funktion GetDataClosedWB

Public Function GetDataClosedWB(SourcePath As String, _
SourceFile As String, sourceSheet As String, _
SourceRange As String, TargetRange As Range) As Boolean
Dim strQuelle       As String
Dim Zeilen          As Long
Dim Spalten         As Byte
On Error GoTo InvalidInput
strQuelle = "'" & SourcePath & "[" & SourceFile & "]" & sourceSheet & "'!" & Range( _
SourceRange).Cells(1, 1).Address(0, 0)
Zeilen = Range(SourceRange).Rows.Count
Spalten = Range(SourceRange).Columns.Count
With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
.Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
.Value = .Value
End With
GetDataClosedWB = True
Exit Function
InvalidInput:
MsgBox "Die Quelldatei oder der Quellbereich ist ungültig!", vbExclamation, "Get data from"
GetDataClosedWB = False
End Function

Public Sub Daten_holen()
Dim Pfad            As String
Dim DateiName       As String
Dim Blatt           As String
Dim Bereich         As String
Dim Ziel            As Range
Pfad = Range("Dateipfad").Value
Application.Calculation = xlCalculationManual
DateiName = Range("Dateiname").Value
Blatt = Range("Tabellenname").Value
Bereich = Range("Kopierbereich").Value
Set Ziel = Tabelle5.Range("K11")
If GetDataClosedWB(Pfad, DateiName, Blatt, Bereich, Ziel) Then
Jahresdaten1_holen
End If
Application.Calculation = xlCalculationAutomatic
End Sub