EXCEL VBA move files from different paths to different path

Question

I have an excel vba code that works very well as seen below, but I want it to move files from different paths to different paths. Can I loop for this?

Example:

sourceFolderPath = "C:\Users\test1" destinationFolderPath = "C:\Users\test2"

sourceFolderPath = "C:\Users\tes3" destinationFolderPath = "C:\Users\test4"

sourceFolderPath = "C:\Users\test5" destinationFolderPath = "C:\Users\test6"


Sub MoveFiles()

    Dim sourceFolderPath As String, destinationFolderPath As StringDim FSO As Object, sourceFolder As Object, file As ObjectDim fileName As String, sourceFilePath As String, destinationFilePath As StringDim strTime As String

    strTime = Format(Now, "yyyymmddhhmm")

    Application.ScreenUpdating = False

    sourceFolderPath = "C:\Users\test"
    destinationFolderPath = "C:\Users\test2"

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set sourceFolder = FSO.Getfolder(sourceFolderPath)

    For Each file In sourceFolder.Files
        fileName = file.Name
        If InStr(fileName, ".xlsx") Or InStr(fileName, ".xls") Then  ' Only xlsx files will be moved
            sourceFilePath = file.Path
            destinationFilePath = destinationFolderPath & "" & strTime & fileName
            FSO.movefile Source:=sourceFilePath, Destination:=destinationFilePath
        End If ' If InStr(sourceFileName, ".xlsx") Then' Only xlsx files will be moved

    Next

    'Don't need set file to nothing because it is initialized in for each loop
    'and after this loop is automatically set to Nothing
    Set sourceFolder = NothingSet FSO = Nothing

End Sub

Add sourceFolderPath and destinationFolderPath as parameters to your Sub MoveFiles, and pass in the values. — Tim Williams
– Tim Williams, Commented Apr 5, 2022 at 15:54
change this row to destinationFilePath = destinationFolderPath & "\" & "" & strTime & fileName — NoobVB
– NoobVB, Commented Apr 5, 2022 at 15:59

Tim Williams · Accepted Answer · 2022-04-05 16:04:29Z

1

From comment above:

Sub Tester()
    MoveFiles "C:\Temp\SO\", "C:\Temp\SO2\"
    MoveFiles "C:\Users\test3\", "C:\Users\test4\"
End Sub

Sub MoveFiles(sourceFolderPath As String, destinationFolderPath As String)
    Dim file As Object, fileName As String, strTime As String

    strTime = Format(Now, "yyyymmddhhmm")
    For Each file In CreateObject("Scripting.FileSystemObject"). _
                                  Getfolder(sourceFolderPath).Files
        fileName = file.Name
        If fileName Like "*.xlsx" Or fileName Like "*.xls" Then
            file.Move destinationFolderPath & "" & strTime & fileName
        End If
    Next
    'really no need to set objects to Nothing when done...
End Sub

answered Apr 5, 2022 at 16:04

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EXCEL VBA move files from different paths to different path

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related