How can I get FileDateTime in VBA to paste UK instead of US date string into a cell in Excel

Question

Using Windows 10 and Microsoft Office 365 set to English(UK), I've written a VBA macro that should paste the last modified time and date of a file into a cell on a spreadsheet. When I paste a date in, in UK format, it seems to be changed to a US date. e.g. the database has a last modified date and time of 11/07/2023 15:43:43 (11th July) and it appears in Excel as 7th November

The code I have is this :

Dim LastModifiedString As String
Dim LastModified As Date
LastModified = FileDateTime("H:\Data\TestDb.accdb")
LastModifiedString = Format(LastModified, "dd/mm/yyyy")
' LastModifiedString is now "11/07/2023 15:43:43"
ActiveSheet.Cells(1, 2) = LastModifiedString

The reason I've tried it this way is so that I can see exactly the string that SHOULD be pasted into the cell.

Answer 1

You could force the cell to accept a string with:

ActiveSheet.Cells(1, 2) = "'" & LastModifiedString

Or you could force the formatting of the cell to accept strings.

ActiveSheet.Cells(1, 2).NumberFormat = "General"
ActiveSheet.Cells(1, 2) = LastModifiedString

Or better yet, set the formatting of the cell to dd/mm/yyyy and just use LastModified without converting to a string along the way:

Dim LastModified As Date
LastModified = FileDateTime("H:\Data\TestDb.accdb")
ActiveSheet.Cells(1, 2).NumberFormat = "dd/mm/yyyy"
ActiveSheet.Cells(1, 2) = LastModified