Sub sub_ReadFiles()
Dim fs, f, f1, fc, strErr, oExcel, oSheet
Dim r As String
Dim iCounter As Integer
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(func_SetPath())
Set fc = f.Files
On Error GoTo fehler
' Set oExcel = New Excel.Application
' Set oExcel = CreateObject("Excel.Application")
iCounter = 0
For Each f1 In fc
If Left(f1.Name, 3) = "WEA" Then
Set oExcel = CreateObject("Excel.Application")
' On Error GoTo fehler_A
strErr = "Can't open file " & f1
oExcel.Workbooks.Open f1
' On Error GoTo fehler
strErr = "Can't find worksheet 'NEG Micon - WW'"
Set oSheet = oExcel.Sheets("NEG Micon - WW")
strErr = "Can't write value to target sheet"
' specify source and target cells here
' source cell in A1 format
' target cell in R1C1 format
' usage: sub_WriteValue(oSheet.Range(source cell), target sheet, target column, target _
row)
Call sub_WriteValue(oSheet.Range("M2"), "Reiter1", 1, 2 + iCounter)
Call sub_WriteValue(oSheet.Range("M3"), "Reiter1", 2, 2 + iCounter)
Call sub_WriteValue(oSheet.Range("Q3"), "Reiter1", 3, 2 + iCounter)
Call sub_WriteValue(oSheet.Range("M4"), "Reiter1", 4, 2 + iCounter)
Call sub_WriteValue(oSheet.Range("M5"), "Reiter1", 5, 2 + iCounter)
Call sub_WriteValue(oSheet.Range("AG2"), "Reiter1", 6, 2 + iCounter)
Call sub_WriteValue(oSheet.Range("AG3"), "Reiter1", 7, 2 + iCounter)
Set oSheet = Nothing
strErr = "Unknown error"
oExcel.Workbooks.Close
marke_A:
iCounter = iCounter + 55
End If
Next
oExcel.Quit
If iCounter = 0 Then
MsgBox "No input files found"
End If
Exit Sub
fehler:
On Error Resume Next
MsgBox (strErr & vbCrLf & vbCrLf & "error number: " & Err.Number _
& vbCrLf & Err.Description & "source: " & Err.Source)
Exit Sub
fehler_A:
On Error Resume Next
MsgBox (strErr & vbCrLf & vbCrLf & "error number: " & Err.Number _
& vbCrLf & Err.Description & "source: " & Err.Source)
GoTo marke_A
End Sub
______________
Nochmal kurz zur Info:
Es geht darum, dass er mir den Dateinamen der Datei ausgibt, die er gerade ausliest und dass in der Spalte AC. Also wenn er eine Datei, die mit "WEA" beginnt ausliest soll er mir den vollständigen Dateiamen in die Zelle AC2 schreiben! Ist das möglich?
Gruß,
Botti