Excel VBA - Using multiple instances of Dir()?

Question

I am using excel to migrate a large file structure into a new folder and re-order many of the folders. I am using the Dir() function to cycle through every folder, and also cycle through files... but I am running into an issue where the second Dir() function overwrites the first. Is there a way to setup two instances of Dir()?

Sub GetFolders()
    Dim oldFolderPath As String
    Dim folder As String
    Dim copyFolderDir As String
    Dim newFolderDir As String
    Dim strFile As String

    oldFolderPath = "C:\Users\jordanharris\Desktop\PATIENT FILES\A\"

    newFolderDir = "C:\Users\jordanharris\Desktop\PATIENT FILES\A v2\"

    'The goal here is to loop through every file in a folder (without knowing how many or their names)
    folder = Dir(oldFolderPath, vbDirectory) 'First Dir()

    Do While folder <> ""
       If (GetAttr(oldFolderPath & folder) And vbDirectory) = vbDirectory Then

           MkDir newFolderDir & folder & "\APPS-AWARDS\"

           copyFolderDir = oldFolderPath & folder & "\DWSS-EA\"

           'The goal here is to copy every file in the folder 'DWSS-EA' to the new folder 'APPS-AWARDS'
           strFile = Dir(copyFolderDir & "*.*") ' This Dir is overwriting the Dir above
           Do While Len(strFile) > 0 
              Name copyFolderDir & strFile As newFolderDir & folder & "\APPS-AWARDS\" & strFile
              'Get next file using Dir
              strFile = Dir()
           Loop
       End If

        'Get Next Folder using Dir 
        folder = Dir() 'Error on this line because Dir is being overwritten
    Loop

End Sub

As you can see, I am using two instances of Dir, which is leading to this error where I cannot go to the next folder. I originally thought I would just put the second instance of Dir in its own Sub, like so...

Sub AppsAwards (newFolderDir As String, oldFolderPath As String, folder As String)
    MkDir newFolderDir & folder & "\BENEFITS\APPS-AWARDS\"

    copyFolderDir = oldFolderPath & folder & "\DWSS-EA\"

    strFile = Dir(copyFolderDir & "*.*")

    Do While Len(strFile) > 0
        Name copyFolderDir & strFile As newFolderDir & folder & "\BENEFITS\APPS-AWARDS\" & strFile
        strFile = Dir()
    Loop
End Sub

... and call this in place of the original code ...

...
AppsAwards newFolderDir, oldFolderPath, folder
...

But it acts exactly the same, the calling of Dir within the sub overwrites the original Dir.

Is there a way to have two instances of Dir()? And if not, is there a workaround for this?

Edit (Solution):

Thanks to Noodles for a good workaround. This is how I implemented it in my code...

Sub ProcessFolder(FolderPath As String, newFolderPath As String)
    On Error Resume Next
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Set fldr = fso.GetFolder(FolderPath)
    Set fls = fldr.Files

    For Each Thing In fls
        Name FolderPath & Thing.Name As newFolderPath & Thing.Name
    Next

End Sub

And then I placed this line in my original code...

...
ProcessFolder oldFolderPath & folder & "\DWSS-EA\", newFolderDir & folder & "\BENEFITS\APPS-AWARDS\"
...

Possible duplicate of [get list of subdirs in vba](http://stackoverflow.com/questions/9827715/get-list-of-subdirs-in-vba) — Comintern, Dec 09 '16 at 20:31
I ran into a similar need and found that I could build arrays. I cycle through one Dir() set looking at folders and assign those directory locations to a variable. Then I could use another Dir() set in each folder path and collect another array of filenames. My file and folder structure for my case is very structured, though, so it may not work for all circumstances. — Tallima, Apr 11 '19 at 18:41

score 3 · Accepted Answer · answered Dec 09 '16 at 20:32

3

You use recursion to walk a tree. This is VBScript so pastable into VBA. PS The help says Visual Basic allows you to process drives, folders, and files in two different ways: through traditional methods such as the Open statement, Write#, and so forth, and through a new set of tools, the File System Object (FSO) object model.

'On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
Dirname = InputBox("Enter Dir name")
'Searchterm = Inputbox("Enter search term")
ProcessFolder DirName

Sub ProcessFolder(FolderPath)
    On Error Resume Next
    Set fldr = fso.GetFolder(FolderPath)

    Set Fls = fldr.files

    For Each thing in Fls
         msgbox Thing.Name & " " & Thing.path 
    Next


    Set fldrs = fldr.subfolders
    For Each thing in fldrs
        ProcessFolder thing.path
    Next

End Sub

answered Dec 09 '16 at 20:32

I've tried this, but the code is never entering the For Loop 'For Each thing in fls' even though there are multiple files in each folder. – Jordan Harris Dec 09 '16 at 21:03
Comment out `On Error Resume Next`. You need to use this in real world as files may be locked or access denied. This is sample code so omits error checking but normally you check errors after every call that may generate (and files are in that category). `If err.number <> 0 then ...` – Dec 09 '16 at 21:11
Yup! Got it working. The problem was that fso needed to be declared inside the function. Got the entire thing working now! – Jordan Harris Dec 09 '16 at 21:13

Excel VBA - Using multiple instances of Dir()?

1 Answers1

Linked