How Can I make this macro look in every folder in a folder?

[tonywatsonhelp]

Hi everyone,

I have this macro that goes to a folder "Patient 47" and converts all the txt to excel files, but I need it to go to all folders not just "Patient 47"
so the fiile path would be: "C:\Users\iplayer\Documents\Odesk New1\41 Odeskrune Hansen mcro\" the every folder in this folder, I don't know what those folders will be called.

is this possible? can someone please show me how to do it?
thanks

Tony

Sub LoopAllFiles()

    Dim sPath As String, sDir As String
    
    sPath = "C:\Users\iplayer\Documents\Odesk New1\41 Odeskrune Hansen mcro\Patient 47"
    
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    
    sDir = Dir$(sPath & "*.txt", vbNormal)
    Do Until Len(sDir) = 0
        Workbooks.Open (sPath & sDir)
        With ActiveWorkbook
            .SaveAs Filename:=Left(.FullName, InStrRev(.FullName, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            .Close
        End With
        
        sDir = Dir$
    Loop
    
End Sub

 

[Gary McMaster]

Hi Tony,

I have the following recursive file collection routine hanging around in my toolbox. I don't remember where I got it or who the author was. It did modify it slightly to output the list to the active worksheet. So you should try it in a new workbook or at least on a new blank worksheet. It was originally sending its output to the debug window

I hope it helps.

Gary

In a standard module:

Option Explicit

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer
Dim oCell As Range

startDir = "C:\Users\iplayer\Documents\Odesk New1\41 Odeskrune Hansen mcro\Patient 47"

Call FillDir(startDir, dlist)

Set oCell = Range("A1")
Application.ScreenUpdating = False

For i = 1 To dlist.Count

oCell.Value = dlist(i)
Set oCell = oCell.Offset(1, 0)

Next i

Application.ScreenUpdating = True


End Sub


Sub FillDir(startDir As String, dlist As Collection)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub

 

[tonywatsonhelp]

Hi Gary,
Thats great, I'm sure if I tinker with this it will do what I need I didn't know were to start so this is a great help, thanks very much

 

[Gary McMaster]

You're very welcome.

I did substitute the "start path" you showed in post #1 in the sample code. As is, it will select every file type. You will also have to change the file type it looks for in the code.

startDir = "C:\Users\iplayer\Documents\Odesk New1\41 Odeskrune Hansen mcro\Patient 47"