Datenimport mit Variablen Pfad

Public Sub Import_Data(wks As Worksheet)
On Error GoTo ErrorHandler
Dim objFileSearch As clsFileSearch
Dim lngIndex As Long, lngCount As Long
Dim varOutput As Variant
Dim strPath As String, strFormula As String
Const cstrTabname As String = "Report" 'Tabellenname
With wks
If .Range("C7") = "" Or .Range("C8") = "" Then
MsgBox ("Bitte Cellen C7 & C8 mit Daten füllen.")
Else
strPath = _
"I:\TS_Dokument\Partikelfallenanalyse\Analyse\" & .Cells(7, 3).Value & "\" & .Cells(8,  _
3).Value & "\"  ' _
Startverzeichnis
If Right(strPath, 1)  "\" Then strPath = strPath & "\"
.Range("B17:C36").ClearContents
Set objFileSearch = New clsFileSearch
With objFileSearch
.NewSearch = True
.CaseSenstiv = False
.Extension = "*.xlsx*"
.FolderPath = strPath
.SearchLike = "*Partikelfallenanalyse_CAR_" & wks.Name & "_*"
.SubFolders = False
If .Execute(Sort_by_Name, Sort_Order_Descending) > 0 Then
ReDim varOutput(1 To 20, 1 To 10)
lngCount = 1
For lngIndex = 1 To .FileCount
If lngCount > 20 Then Exit For
strFormula = "='" & .Files(lngIndex).FI_FolderPath
strFormula = strFormula & "[" & .Files(lngIndex).FI_FileName & "]"
strFormula = strFormula & cstrTabname & "'!"
varOutput(lngCount, 1) = strFormula & "U50"
varOutput(lngCount, 2) = strFormula & "I19"
varOutput(lngCount, 3) = strFormula & "I20"
varOutput(lngCount, 4) = strFormula & "I21"
varOutput(lngCount, 5) = strFormula & "I22"
varOutput(lngCount, 6) = strFormula & "I23"
varOutput(lngCount, 7) = strFormula & "I24"
varOutput(lngCount, 8) = strFormula & "I25"
varOutput(lngCount, 9) = strFormula & "I26"
varOutput(lngCount, 10) = strFormula & "U46"
lngCount = lngCount + 1
Next
End If
End With
With .Range("B17").Resize(UBound(varOutput, 1), 10)
.Formula = varOutput
.Value = .Value
End With
Set objFileSearch = Nothing
End If
End With
Exit Sub
ErrorHandler:
MsgBox ""
End Sub

Public Sub Import_Data(wks As Worksheet)
On Error GoTo ErrorHandler
Dim objFileSearch As clsFileSearch
Dim lngIndex As Long, lngCount As Long
Dim varOutput As Variant
Dim strPath As String, strFormula As String
Const cstrTabname As String = "Report" 'Tabellenname
With wks
If .Range("C7") = "" Or .Range("C8") = "" Then
MsgBox ("Bitte Cellen C7 & C8 mit Daten füllen.")
Else
strPath = _
"I:\TS_Dokument\Partikelfallenanalyse\Analyse\" & .Cells(7, 3).Value & "\" & .Cells(8,  _
3).Value & "\"  ' _
Startverzeichnis
If Right(strPath, 1)  "\" Then strPath = strPath & "\"
.Range("B17:C36").ClearContents
Set objFileSearch = New clsFileSearch
With objFileSearch
.NewSearch = True
.CaseSenstiv = False
.Extension = "*.xlsx*"
.FolderPath = strPath
.SearchLike = "*Partikelfallenanalyse_" & .Cells(7, 3).Value & "_" & wks.Name & "_*"
.SubFolders = False
If .Execute(Sort_by_Name, Sort_Order_Descending) > 0 Then
ReDim varOutput(1 To 20, 1 To 10)
lngCount = 1
For lngIndex = 1 To .FileCount
If lngCount > 20 Then Exit For
strFormula = "='" & .Files(lngIndex).FI_FolderPath
strFormula = strFormula & "[" & .Files(lngIndex).FI_FileName & "]"
strFormula = strFormula & cstrTabname & "'!"
varOutput(lngCount, 1) = strFormula & "U50"
varOutput(lngCount, 2) = strFormula & "I19"
varOutput(lngCount, 3) = strFormula & "I20"
varOutput(lngCount, 4) = strFormula & "I21"
varOutput(lngCount, 5) = strFormula & "I22"
varOutput(lngCount, 6) = strFormula & "I23"
varOutput(lngCount, 7) = strFormula & "I24"
varOutput(lngCount, 8) = strFormula & "I25"
varOutput(lngCount, 9) = strFormula & "I26"
varOutput(lngCount, 10) = strFormula & "U46"
lngCount = lngCount + 1
Next
End If
End With
With .Range("B17").Resize(UBound(varOutput, 1), 10)
.Formula = varOutput
.Value = .Value
End With
Set objFileSearch = Nothing
End If
End With
Exit Sub
ErrorHandler:
MsgBox ""
End Sub