Apr 12 2023 06:13 PM
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.
Apr 12 2023 07:18 PM
@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.
Apr 12 2023 11:03 PM - edited Apr 12 2023 11:22 PM
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.
Apr 13 2023 06:55 PM
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.
Apr 16 2023 08:08 AM
Apr 13 2023 06:55 PM
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.