Get system's date delimiter

user_235671 0
Viewed 318

I need to import various Excel files into an Access database. There are two date columns that use all possible formattings like 01.05.2020 or 01,05,2020 or 01/05/2020 depending on how they were created. Sometimes it's valid date and another time it's just a string.

The Access application will be running on different systems too, so they might be using different delimiters. This means I cannot just replace all . or , with /. I am going to create a replace function that would normalize the string for whatever system it is running on but I cannot figure out how to get the current system's date delimiter.

Is VBA able to give me that information?

office365 ms-access vba excel
2 Answers

Use the XlApplicationInternational enum with the Application.International property to retrieve this configuration.

This returns / on my system:

Excel.Application.International(xlApplicationInternational.xlDateSeparator)

If you're in an Access host, you'll want to avoid using the global Excel.Application object, and use an instance that you own and that you can Quit once you're done:

Dim app As Excel.Application
Set app = New Excel.Application
Debug.Print app.International(xlApplicationInternational.xlDateSeparator)
'...
app.Quit
user_11885130

The truly simple and fastest method is to let VBA do the job as the character "/" to Format designates the date separator:

' Get localised date separator.
DateSeparator = Format(Date, "/")

' Denmark -> "-"
' Germany -> "."
' US      -> "/"

Why this works:

(/) - Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your system settings.

from User-defined date/time formats

user_35272970
Related