CODEWITHSUNDEEP
vbams-access
James
James

Reputation: 323

Check for date in filename

I don't know why but I cannot get this to work. I am am a VBA amateur so this maybe a simple fix. I am trying to check if a file exists with the current date (so with today's date on the end of the file).

I have a test .txt file called "test_doc". I can find this using If file.FileExists(Location & ".txt") which works and returns "File exists!.

However when I add in MyDate and try to search for the file with the date on the end (e.g. "test_doc05072020") it returns "File does NOT exist".

My VBA:

Dim file: Set file = CreateObject("Scripting.FileSystemObject")

Dim Location
Location = "C:\Desktop\database\test_doc"

Dim MyDate
MyDate = Format(Date, "dd/mm/yyyy")

If file.FileExists(Location & MyDate & ".txt") Then
    MsgBox "File exists!"
Else
    MsgBox "File does NOT exist"
End If

I have looked all over but cannot find the right help to this. I don't know why it's not picking up the date?

Upvotes: 0

Views: 452

Answers (1)

Applecore
Applecore

Reputation: 4099

Your problem is how you are formatting the date. As it stands, you are making the filename "test_doc05/07/2020.doc". Instead, try:

Dim MyDate As String
MyDate = Format(Date, "ddmmyyyy")

Note that I have declared MyDate as a string - in your code this is a variant by default.

Regards,

Upvotes: 1

Related Questions