Moving files

[tez_field]

Hi,

I am running a macro to iterate through a folder of files, run a lookup on a spreadsheet to find three levels of folder names, and if they dont exist, create the folders then move the file to the folder. The code I have is below.

If Not Dir([loc_dump].Value, vbDirectory) = vbNullString Then
    ChDir sPath
    
    sFil = Dir("")
    
    Do While sFil <> ""
        
        folder1 = "\" & Worksheets("Files").Cells(findRow("Files", 1, sFil, 2, Worksheets("Files").Cells(65000, 1).End(xlUp).Row), 2).Value
        folder2 = "\" & Worksheets("Files").Cells(findRow("Files", 1, sFil, 2, Worksheets("Files").Cells(65000, 1).End(xlUp).Row), 3).Value
        folder3 = "\" & Worksheets("Files").Cells(findRow("Files", 1, sFil, 2, Worksheets("Files").Cells(65000, 1).End(xlUp).Row), 4).Value
        
        newpath = [loc_dump].Value & folder1
        If Dir(newpath, vbDirectory) = vbNullString Then MkDir (newpath)
        
        If folder2 <> "\" Then
            newpath = newpath & folder2
            If Dir(newpath, vbDirectory) = vbNullString Then MkDir (newpath)
        End If
        
        If folder3 <> "\" Then
            newpath = newpath & folder3
            If Dir(newpath, vbDirectory) = vbNullString Then MkDir (newpath)
        End If
        
        Debug.Print sFil & " - " & newpath
        
        sFil = Dir
    Loop
End If

The problem I am having, is that it creates the folders for the first file, but errors after that. The reason is that once I run the

If Dir(newpath, vbDirectory) = vbNullString Then MkDir (newpath)

line, the original sFil = Dir("") forgets where it is. Is there any way of getting around this?

Thanks,

 

[xenou]

You could try moving the DIR call out to another function or sub. It may reset on each call that way. In any case, you can't call this function on a different folder and then go back to an earlier DIR call, if that's what you want to do (you *might* be able to create recursive calls to the same function but with DIR I'm not sure).

Simpler might be to create all the folders first- you don't need to know if they exist. If they do, the MKDIR will fail (*I think*) so no worries - just create all the folders first, wrapping the command in an On Error Resume Next statement.

ξ

 

[tez_field]

Thanks, that's pretty much what I did in the end, I stored the filenames and three folders in a 2d array then after that loop and went back and created the folders and copied the files

Thanks!