Macro fails due to date format

[JeffGrant]

Hi All,
I have this small macro that copies some files on the hard drive for a backup. Basically it looks for a folder and file with the date as part of the name. No drama. But today it fell over because with the date format of dd-mmm-yyyy, is all good unless the date, like today is less than the 10th of the month. so i need a date format of d-mmm-yyyy.

an example of the folder name is: C:\Users\jeff\Desktop\Current_Results_10\Output_01-Jun-2023)
an example of the file name is: \Main-Report-01-Jun-2023.csv
so when the name of the folder and file is from the 1st to the 9th of the month the vba rightly returns an error of "file cannot be found"

is there a way to make this a liitle more "general" so that I dont have ot change the date format manually or change the name of the folder and file manually?

Here is the code.

As always, your gracious help is always greatly appreciated.

Sub CopyMainReportToAllForm()

    'Declare Variables
    Dim FSO
    Dim sFile As String
    Dim sSFolder As String
    Dim sDFolder As String

    'This is the file to copy
    sFile = "\Main-Report-" & Format(Date, "dd-mmm-yyyy") & ".csv"

    'Changing to match the source folder path
    sSFolder = "C:\Users\jeff\Desktop\Current_Results_10\Output_" & Format(Date, "dd-mmm-yyyy")
    
    'Changing to match the destination folder path
    sDFolder = "C:\Users\jeff\Desktop\Current_Results_10\All Form\Main-Report-" & Format(Date, "dd-mmm-yyyy") & ".csv"
    
    'Create Object
    Set FSO = CreateObject("Scripting.FileSystemObject")

    'Copy the file
    FSO.CopyFile (sSFolder & sFile), sDFolder

End Sub

 

[MARK858]

Does the date format d-mm-yyyy not work for you for both the single and double digit days?

 

[JeffGrant]

Thanks Mark. I have always assumed, all be it incorrectly, that the single "d "in the format would only pick up days 1 to 9., not dats 10 ro 31.

I will do as you suggest and see what happens on the 10th of June.

cheers

 

[MARK858]

The results should be as per the table below

Book1.xlsb
	A	B
1	dd/mm/yyyy	d/mm/yyyy
2	01/06/2023	1/06/2023
3	02/06/2023	2/06/2023
4	03/06/2023	3/06/2023
5	04/06/2023	4/06/2023
6	05/06/2023	5/06/2023
7	06/06/2023	6/06/2023
8	07/06/2023	7/06/2023
9	08/06/2023	8/06/2023
10	09/06/2023	9/06/2023
11	10/06/2023	10/06/2023
12	11/06/2023	11/06/2023
13	12/06/2023	12/06/2023
14	13/06/2023	13/06/2023
15	14/06/2023	14/06/2023
16	15/06/2023	15/06/2023
17	16/06/2023	16/06/2023
18	17/06/2023	17/06/2023
19	18/06/2023	18/06/2023
20	19/06/2023	19/06/2023
21	20/06/2023	20/06/2023
22	21/06/2023	21/06/2023
23	22/06/2023	22/06/2023
24	23/06/2023	23/06/2023
25	24/06/2023	24/06/2023
26	25/06/2023	25/06/2023
27	26/06/2023	26/06/2023
28	27/06/2023	27/06/2023
29	28/06/2023	28/06/2023
30	29/06/2023	29/06/2023
31	30/06/2023	30/06/2023
32	01/07/2023	1/07/2023
33	02/07/2023	2/07/2023
Sheet1

 

[Biz]

Nice

 

[JeffGrant]

Hi Mark858,

well we are now 3 days into double digit day format and everything is working well.

Thanks for fixing that up. Clearly, my assumption about date format needs a bit of updating

 

[Alex Blakenburg]

In terms of file names and folder names you really want to consider switching to the format yyyy-mm-dd.

The folders and files names sort as text from left to right and you will have all the days across months and years sorting together eg 01 Jan 2022, 01 Jan 2023, 01 Jan 2023 then 02 Jan 2022, 02 Jan 2023, 02 Jan 2024
(you will have 01 where I have Jan)
This makes it very hard to find things back not to mention that it looks untidy.

 

[JeffGrant]

Excellent point Alex...

I will look at that.

cheers

 

[MARK858]

Just as a general point about the suggestion by Alex.

The date format yyyy-mm-dd is better all round as it is the only date format that is recognised as the correct date in most regions, as it is the international date format (ISO 8601).

It just saves having most of the conversion issues that you see in various posts, and so is much better if people share files with different countries