Forum Discussion

Brass Contributor

Apr 12, 2023

Solved

Help on macro

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...

Macros and VBA

Rodrigo_

Apr 13, 2023

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.

Rodrigo_

Steel Contributor

Apr 12, 2023

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.

A2Z CF

Brass Contributor

Apr 12, 2023

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.

Rodrigo_

Steel Contributor

Apr 13, 2023

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

A2Z CF
Brass Contributor
Apr 16, 2023
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.