Forum Discussion

A2Z CF's avatar
A2Z CF
Brass Contributor
Apr 13, 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 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.

Macros and VBA
  • Rodrigo_'s avatar
    Rodrigo_
    Apr 14, 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_'s avatar
    Rodrigo_
    Steel Contributor
    Apr 13, 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's avatar
      A2Z CF
      Brass Contributor
      Apr 13, 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_'s avatar
        Rodrigo_
        Steel Contributor
        Apr 14, 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.

Resources