Using VBA moving files from one folder to another

[gjsala]

I would like using VBA to move files from one folder to another. The files moved are time stamped greater than 48 hours old. Any help would be greatly appreciated.

Thanks!

 

[Domenic]

Try the following macro, which needs to be placed in a regular module...

Option Explicit

Sub MoveFiles()

    Dim strFolderA As String
    Dim strFolderB As String
    Dim strFile As String
    Dim Cnt As Long

    '//Change the path to the source folder, accordingly
    strFolderA = "C:\Path\FolderA\"
    
    '//Change the path to the destination folder, accordingly
    strFolderB = "C:\Path\FolderB\"
    
    If Right(strFolderA, 1) <> "\" Then strFolderA = strFolderA & "\"
    If Right(strFolderB, 1) <> "\" Then strFolderB = strFolderB & "\"
    
    '//To filter for .xlsx files, change "*.*" to "*.xlsx"
    strFile = Dir(strFolderA & "*.*")
    
    Do While Len(strFile) > 0
        If Date - Int(FileDateTime(strFolderA & strFile)) > 2 Then
            Cnt = Cnt + 1
            Name strFolderA & strFile As strFolderB & strFile
        End If
        strFile = Dir
    Loop
    
    MsgBox Cnt & " file(s) have been transfered to " & strFolderB, vbInformation
        
End Sub

 

[Domenic]

Actually, the macro selects files where the difference between today's date (without the time) and the file's date (without the time) is greater than 2 days. If you want to select files where you'd like to take the actual time of day into consideration, replace...

If Date - Int(FileDateTime(strFolderA & strFile)) > 2 Then

with

If (Date + Time) - FileDateTime(strFolderA & strFile) > 2 Then

 

[gjsala]

This work very well! Thank you very much...

 

[MPW]

Do you know how to have the loop continue before the file has finished moving?

I would like to speed up the process by having the code advance as soon as the command is given.

 

[kbriaz]

Try the following macro, which needs to be placed in a regular module...

Option Explicit

Sub MoveFiles()

    Dim strFolderA As String
    Dim strFolderB As String
    Dim strFile As String
    Dim Cnt As Long

    '//Change the path to the source folder, accordingly
    strFolderA = "C:\Path\FolderA\"
    
    '//Change the path to the destination folder, accordingly
    strFolderB = "C:\Path\FolderB\"
    
    If Right(strFolderA, 1) <> "\" Then strFolderA = strFolderA & "\"
    If Right(strFolderB, 1) <> "\" Then strFolderB = strFolderB & "\"
    
    '//To filter for .xlsx files, change "*.*" to "*.xlsx"
    strFile = Dir(strFolderA & "*.*")
    
    Do While Len(strFile) > 0
        If Date - Int(FileDateTime(strFolderA & strFile)) > 2 Then
            Cnt = Cnt + 1
            Name strFolderA & strFile As strFolderB & strFile
        End If
        strFile = Dir
    Loop
    
    MsgBox Cnt & " file(s) have been transfered to " & strFolderB, vbInformation
        
End Sub

Hi,

How would you change this code so it copies a file from one location to another & re-names it ?

File.xls from C:\test to 'File_.xls' at C:\Done

Please ?

Excel noob here - ta