Solved: Subfolders

[khalid79m]

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

[Bob Phillips]

You need to use FileSystemObject to read all subfolders.

How is the weeknumber derived?

[khalid79m]

can you assit with changing this code to suit a filesystemobject as I have no idea how to do that

[GTO]

...How is the weeknumber derived?

[khalid79m]

all calls are tagged with a wk? number, when distributed, once they are listened to they are then transfered automaticlly into the appropriate wk folder and stored as a delimited | text file

[khalid79m]

( I have a form which a senior will type in the agent pin and select a site m, this will load a selection of calls all are tagged with a week no, the week no is the week it is distributed for)