Moving bulk files in VBA with FSO

[anichols]

Hi all,

I have a code that moves a batch of files from one folder to another. my issue is when I try to change one of the locations to a cell reference, I end up with run time error 5.
Any help with what is likely a simple oversight would be greatly appreciated!

Sub Move_Aires()
Dim FSO As New FileSystemObject
Dim WhereTo As String
Set FSO = CreateObject("Scripting.FileSystemObject")
WhereTo = Sheets("ARIES_REG").Range("D4").Value
FSO.MoveFile "P:\Servicing\are*.are", WhereTo
'FSO.MoveFile "P:\Servicing\are*.are", "P:\Servicing - 3. Batch Files\31. Aires\2022\2022.09.01\"

End Sub

 

[Domenic]

Does Sheets("ARIES_REG").Range("D4").Value contain a value? If so, does it contain a valid path?

 

[anichols]

Does Sheets("ARIES_REG").Range("D4").Value contain a value? If so, does it contain a valid path?

Yes. the commented out line contains the value from that cell instead. When I run it that way it is successful.

 

[Domenic]

I would suggest that you amend your code so it checks that both the source folder and destination folder exist before moving your files.

By the way, it looks like you're using early binding. As such, there's no need for Set FSO = CreateObject("Scripting.FileSystemObject"), since you are already creating an instance of the FileSystemObject object with Dim FSO As New FileSystemObject.

So your code can be re-written as follows...

Sub Move_Aires()

    Dim FSO As New FileSystemObject
    Dim WhereFrom As String
    Dim WhereTo As String
   
    WhereFrom = "P:\Servicing\"
    If Right(WhereFrom, 1) <> "\" Then
        WhereFrom = WhereFrom & "\"
    End If
   
    If Not FSO.FolderExists(WhereFrom) Then
        MsgBox "The folder does not exist: " & vbCrLf & vbCrLf & WhereFrom, vbExclamation
        Exit Sub
    End If
   
    WhereTo = Sheets("ARIES_REG").Range("D4").Value
   
    If Not FSO.FolderExists(WhereTo) Then
        MsgBox "The folder does not exist: " & vbCrLf & vbCrLf & WhereTo, vbExclamation
        Exit Sub
    End If
   
    FSO.MoveFile WhereFrom & "are*.are", WhereTo

End Sub

However, if the source folder does not contain any .are files, you'll get the following error...

Run-time error '53':

File not found

It probably would be better to loop through each file in the source folder, and check whether the file extension is .are. If so, move the file to the destination folder.

Hope this helps!

 

[anichols]

I would suggest that you amend your code so it checks that both the source folder and destination folder exist before moving your files.

By the way, it looks like you're using early binding. As such, there's no need for Set FSO = CreateObject("Scripting.FileSystemObject"), since you are already creating an instance of the FileSystemObject object with Dim FSO As New FileSystemObject.

So your code can be re-written as follows...

Sub Move_Aires()

    Dim FSO As New FileSystemObject
    Dim WhereFrom As String
    Dim WhereTo As String
  
    WhereFrom = "P:\Servicing\"
    If Right(WhereFrom, 1) <> "\" Then
        WhereFrom = WhereFrom & "\"
    End If
  
    If Not FSO.FolderExists(WhereFrom) Then
        MsgBox "The folder does not exist: " & vbCrLf & vbCrLf & WhereFrom, vbExclamation
        Exit Sub
    End If
  
    WhereTo = Sheets("ARIES_REG").Range("D4").Value
  
    If Not FSO.FolderExists(WhereTo) Then
        MsgBox "The folder does not exist: " & vbCrLf & vbCrLf & WhereTo, vbExclamation
        Exit Sub
    End If
  
    FSO.MoveFile WhereFrom & "are*.are", WhereTo

End Sub

However, if the source folder does not contain any .are files, you'll get the following error...

Run-time error '53':

File not found

It probably would be better to loop through each file in the source folder, and check whether the file extension is .are. If so, move the file to the destination folder.

Hope this helps!

Awesome, thank you!

 

[Domenic]

You're very welcome, glad I could help.

Cheers!