AW: Fehler abfangen wenn Tabellenblatt nicht vorha
04.09.2006 22:40:16
fcs
Hallo Dieter,
ich habe ich hier mal 2 mögliche Lösungen gebastelt (ungetestet), wobei ich mir nicht sicher bin ob die 2. auch funktioniert.
Bei Lösung 1 wird die Datei kurz geöffnet, das Vorhandensein der beiden Blätter geprüft und die Datei wieder geschlossen.
Bei Lösung 2 werden 2 Zellen testweise mit den Formeln ausgefüllt und dann geprüft, ob das Ergebnis einen Fehlerwert ergibt.
Gruss
Franz
Sub Einlesen()
Dim iCounter As Integer, iRow As Integer, i As Integer
Dim sfile As String, sPath As String, wb As Workbook, wks As Worksheet
Dim Kundendaten As Boolean, Info As Boolean
iRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
With Application.FileSearch
.LookIn = Range("AA1").Value
.FileType = msoFileTypeExcelWorkbooks
.Execute
For iCounter = 1 To .FoundFiles.Count
Kundendaten = False
Info = False
Application.ScreenUpdating = False
Set wb = Workbooks.Open(FileName:=.FoundFiles(iCounter), ReadOnly:=True)
For Each wks In wb.Worksheets
If wks.Name = "Kundendaten" Then Kundendaten = True
If wks.Name = "Info" Then Info = True
Next wks
wb.Close savechanges:=False
Application.ScreenUpdating = True
If Kundendaten = True And Info = True Then
sfile = Dir(.FoundFiles(iCounter))
sPath = WorksheetFunction.Substitute(.FoundFiles(iCounter), sfile, "")
For i = 6 To 8
With Cells(iRow, i - 5)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E" & i
.Value = .Value
End With
Next
For i = 14 To 22
With Cells(iRow, i - 10)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E" & i
.Value = .Value
End With
Next
With Cells(iRow, 13)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E24"
.Value = .Value
End With
With Cells(iRow, 14)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E26"
.Value = .Value
End With
With Cells(iRow, 15)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!F6"
.Value = .Value
End With
With Cells(iRow, 16)
.Formula = "='" & sPath & "[" & sfile & "]Info'!C2"
.Value = .Value
End With
iRow = iRow + 1
End If
Next iCounter
End With
End Sub
Sub Einlesen()
Dim iCounter As Integer, iRow As Integer, i As Integer
Dim sfile As String, sPath As String, Kundendaten As Boolean, Info As Boolean
iRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
With Application.FileSearch
.LookIn = Range("AA1").Value
.FileType = msoFileTypeExcelWorkbooks
.Execute
For iCounter = 1 To .FoundFiles.Count
sfile = Dir(.FoundFiles(iCounter))
sPath = WorksheetFunction.Substitute(.FoundFiles(iCounter), sfile, "")
Kundendaten = True
Info = True
i = 6
With Cells(iRow, i - 5)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E" & i
If IsError(.Value) Then Kundendaten = False
.ClearContents
End With
With Cells(iRow, 16)
.Formula = "='" & sPath & "[" & sfile & "]Info'!C2"
If IsError(.Value) Then Info = False
.ClearContents
End With
If Kundendaten = True And Info = True Then
For i = 6 To 8
With Cells(iRow, i - 5)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E" & i
.Value = .Value
End With
Next
For i = 14 To 22
With Cells(iRow, i - 10)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E" & i
.Value = .Value
End With
Next
With Cells(iRow, 13)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E24"
.Value = .Value
End With
With Cells(iRow, 14)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!E26"
.Value = .Value
End With
With Cells(iRow, 15)
.Formula = "='" & sPath & "[" & sfile & "]Kundendaten'!F6"
.Value = .Value
End With
With Cells(iRow, 16)
.Formula = "='" & sPath & "[" & sfile & "]Info'!C2"
.Value = .Value
End With
iRow = iRow + 1
End If
Next iCounter
End With
End Sub