' **********************************************************************
' Modul: Modul2 Typ: Allgemeines Modul
' **********************************************************************
Option Explicit
Sub getData()
Dim strPath As String, strFile As String, strTab As String, strFormula As String
Dim varRanges As Variant
Dim lngDate As Long, lngEnd As Long, lngI As Long, lngR As Long
On Error GoTo ErrorHandler
With Application
.ScreenUpdating = False
.EnableEvents = False
.AskToUpdateLinks = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
varRanges = Array("A15", "D16", "D17", "D18")
lngEnd = IIf(Month(DateSerial(Year(Date), 2, 29)) = 3, 365, 366)
strPath = "E:\Forum\" 'Stammverzeichnis
strTab = "Tabelle1" 'Tabellenname
With ThisWorkbook.Sheets("Tabelle2") 'Name der Ausgabetabelle
For lngDate = 1 To lngEnd
strFile = Dir(strPath & Format(DateSerial(Year(Date), 1, lngDate), "DD_MM") & "\*.xlsx", vbNormal)
If strFile <> "" Then
lngR = lngR + 1
strFormula = "='" & strPath & Format(DateSerial(Year(Date), 1, lngDate), "DD_MM") & "\[" & strFile & "]" & strTab & "'!"
For lngI = 0 To UBound(varRanges)
.Cells(lngR, 5 + lngI).Formula = strFormula & varRanges(lngI)
Next
End If
Next
.Range("E1").Resize(lngR, 4) = .Range("E1").Resize(lngR, 4).Value
End With
ErrorHandler:
If Err.Number <> 0 Then
MsgBox "Fehler in Modul2" & vbLf & vbLf & "Prozedur:" & vbTab & "getData" & vbLf & _
"Nummer:" & vbTab & Err.Number & vbLf & "Meldung:" & vbTab & Err.Description & vbLf & _
IIf(Erl, "Zeile:" & vbTab & Erl, ""), vbExclamation, "Fehler!"
Err.Clear
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.AskToUpdateLinks = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
End Sub