AW: Dateien auslesen
13.01.2008 13:30:03
Erich
Hallo Gerold,
yogis Ansatz etwas weiterentwickelt (findet aber immer nur das erste Auftreten des Suchbegriffs _
im durchsuchten Blatt):
Option Explicit
Sub suchen()
Dim wsResult As Worksheet, strVerz As String, strDat As String
Dim rngSuch As Range, rngFound As Range, lngZ As Long, lngRes As Long
Set wsResult = ThisWorkbook.Worksheets("Resultate")
strVerz = "C:\Temp\"
strDat = Dir(strVerz & "*.xls")
Application.ScreenUpdating = False
Application.EnableEvents = False
Do Until strDat = ""
Application.StatusBar = strDat
Workbooks.Open strVerz & strDat, 0, True
With Worksheets("ToDo").Range("B:B") ' Suchgebiet
Set rngFound = .Find("abc", lookat:=xlPart) ' Suche
End With
If Not rngFound Is Nothing Then
lngZ = rngFound.Row
lngRes = lngRes + 1
With wsResult
.Cells(lngRes, 1) = strDat
.Range(.Cells(lngRes, 2), .Cells(lngRes, 256)) = _
Range(Cells(lngZ, 1), Cells(lngZ, 255)).Value
End With
End If
ActiveWorkbook.Close False
strDat = Dir
If Not rngFound Is Nothing Then
Application.ScreenUpdating = True
Application.ScreenUpdating = False
End If
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort