SOLVED

Help on macro

Brass Contributor

Hi everyone,

I need help generating the macro that moves three Excel files from one location to another (source to destination). But the problem I’m having is the source file names changes every day due to the current date. Therefore, the names of those files are not fixed as always. How could it be possible to move those files from source to destination while keeping the current date format as is. I’ll appreciate any help this regard very much.

Thank you.

4 Replies

@A2Z CF 

Here is an example of macro that moves three Excel files from a source folder to a destination folder, taking into account the changing file names based on the current date:

Sub MoveFiles()
    Dim SourceFolder As String
    Dim DestinationFolder As String
    Dim File1 As String
    Dim File2 As String
    Dim File3 As String
    
    ' Set the source folder path
    SourceFolder = "C:\SourceFolder\"
    
    ' Set the destination folder path
    DestinationFolder = "C:\DestinationFolder\"
    
    ' Get the current date
    Dim currentDate As String
    currentDate = Format(Date, "yyyymmdd")
    
    ' Generate the file names based on the current date
    File1 = "File1_" & currentDate & ".xlsx"
    File2 = "File2_" & currentDate & ".xlsx"
    File3 = "File3_" & currentDate & ".xlsx"
    
    ' Move the files from source to destination
    FileCopy SourceFolder & File1, DestinationFolder & File1
    FileCopy SourceFolder & File2, DestinationFolder & File2
    FileCopy SourceFolder & File3, DestinationFolder & File3
    
    ' Delete the files from the source folder
    Kill SourceFolder & File1
    Kill SourceFolder & File2
    Kill SourceFolder & File3
    
    MsgBox "Files moved successfully!", vbInformation
End Sub

 

*Note: Remember to update the source and destination folder paths in the macro to match your actual file locations.

Thank you so much for your great help. This works very nice except for one thing if it could be possibly fixed. There are three different destinations for the files to be moved and I usually move them after midnight (12:00 AM), when the date changes, so how could I keep the "previous" day's date in the file name rather than the new date?
Thanks again and I appreciate it very much.

best response confirmed by A2Z CF (Brass Contributor)
Solution

@A2Z CF 

This may work to capture the previous day's date and use it in the file names. See below the updated version that takes into account the previous date:

Sub MoveFiles()
    Dim SourceFolder As String
    Dim DestinationFolder1 As String
    Dim DestinationFolder2 As String
    Dim DestinationFolder3 As String
    Dim File1 As String
    Dim File2 As String
    Dim File3 As String
    
    ' Set the source folder path
    SourceFolder = "C:\SourceFolder\"
    
    ' Set the destination folder paths
    DestinationFolder1 = "C:\DestinationFolder1\"
    DestinationFolder2 = "C:\DestinationFolder2\"
    DestinationFolder3 = "C:\DestinationFolder3\"
    
    ' Get the previous day's date
    Dim previousDate As Date
    previousDate = Date - 1
    
    ' Generate the file names based on the previous day's date
    File1 = "File1_" & Format(previousDate, "yyyymmdd") & ".xlsx"
    File2 = "File2_" & Format(previousDate, "yyyymmdd") & ".xlsx"
    File3 = "File3_" & Format(previousDate, "yyyymmdd") & ".xlsx"
    
    ' Move the files from source to destination
    FileCopy SourceFolder & File1, DestinationFolder1 & File1
    FileCopy SourceFolder & File2, DestinationFolder2 & File2
    FileCopy SourceFolder & File3, DestinationFolder3 & File3
    
    ' Delete the files from the source folder
    Kill SourceFolder & File1
    Kill SourceFolder & File2
    Kill SourceFolder & File3
    
    MsgBox "Files moved successfully!", vbInformation
End Sub


That will now calculates the previous day's date by using date function and subtracting 1 from it, and by using format function to generate the file names based on previous date. and then moves the files to the respective destination folders.

Sorry for the delayed response. This macro works great. Thank you so much for the updated macro. I appreciate your kind help very much. May the power be with you!
Thanks again.
1 best response

Accepted Solutions
best response confirmed by A2Z CF (Brass Contributor)
Solution

@A2Z CF 

This may work to capture the previous day's date and use it in the file names. See below the updated version that takes into account the previous date:

Sub MoveFiles()
    Dim SourceFolder As String
    Dim DestinationFolder1 As String
    Dim DestinationFolder2 As String
    Dim DestinationFolder3 As String
    Dim File1 As String
    Dim File2 As String
    Dim File3 As String
    
    ' Set the source folder path
    SourceFolder = "C:\SourceFolder\"
    
    ' Set the destination folder paths
    DestinationFolder1 = "C:\DestinationFolder1\"
    DestinationFolder2 = "C:\DestinationFolder2\"
    DestinationFolder3 = "C:\DestinationFolder3\"
    
    ' Get the previous day's date
    Dim previousDate As Date
    previousDate = Date - 1
    
    ' Generate the file names based on the previous day's date
    File1 = "File1_" & Format(previousDate, "yyyymmdd") & ".xlsx"
    File2 = "File2_" & Format(previousDate, "yyyymmdd") & ".xlsx"
    File3 = "File3_" & Format(previousDate, "yyyymmdd") & ".xlsx"
    
    ' Move the files from source to destination
    FileCopy SourceFolder & File1, DestinationFolder1 & File1
    FileCopy SourceFolder & File2, DestinationFolder2 & File2
    FileCopy SourceFolder & File3, DestinationFolder3 & File3
    
    ' Delete the files from the source folder
    Kill SourceFolder & File1
    Kill SourceFolder & File2
    Kill SourceFolder & File3
    
    MsgBox "Files moved successfully!", vbInformation
End Sub


That will now calculates the previous day's date by using date function and subtracting 1 from it, and by using format function to generate the file names based on previous date. and then moves the files to the respective destination folders.

View solution in original post