khalid79m
02-19-2010, 04:15 AM
Dim ln$, a%, b%, c%, xRow%, xFile$, xFilesProcessed%
AppFolder = "\\ssd\dddss\import\
ThisWorkbook.Sheets("Data").Activate
xFile = Dir(AppFolder & "WK7\" & "*.TXT")
xRow = 1
Do Until xFile = ""
Open AppFolder & "WK7\" & xFile For Input As #1
Do Until EOF(1)
Line Input #1, ln
xRow = ThisWorkbook.Sheets("Data").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
b = 0
Sheets("Data").Cells(xRow, 1) = xFile
For a = 1 To 5
c = InStr(b + 1, ln, "|")
Sheets("Data").Cells(xRow, a + 1) = Mid(ln, b + 1, c - b - 1)
b = c
Next
Loop
Close #1
xFile = Dir()
Loop
End Sub
my code above goes into the selected folder wk7 and looks at all the files in this folder and then transfer the data based on pipe delimited txt files. This works a treat.
However I want to change this so that it doesnt look at wk7 folder but to look through all the sub folders within the import folder. The import folder contains wk1 to wk52. Point to note a week folder wont be created in the import folder until the week actually commences, so for this year we are on week 8. if that makes sense.
Can anyone help
AppFolder = "\\ssd\dddss\import\
ThisWorkbook.Sheets("Data").Activate
xFile = Dir(AppFolder & "WK7\" & "*.TXT")
xRow = 1
Do Until xFile = ""
Open AppFolder & "WK7\" & xFile For Input As #1
Do Until EOF(1)
Line Input #1, ln
xRow = ThisWorkbook.Sheets("Data").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
b = 0
Sheets("Data").Cells(xRow, 1) = xFile
For a = 1 To 5
c = InStr(b + 1, ln, "|")
Sheets("Data").Cells(xRow, a + 1) = Mid(ln, b + 1, c - b - 1)
b = c
Next
Loop
Close #1
xFile = Dir()
Loop
End Sub
my code above goes into the selected folder wk7 and looks at all the files in this folder and then transfer the data based on pipe delimited txt files. This works a treat.
However I want to change this so that it doesnt look at wk7 folder but to look through all the sub folders within the import folder. The import folder contains wk1 to wk52. Point to note a week folder wont be created in the import folder until the week actually commences, so for this year we are on week 8. if that makes sense.
Can anyone help