I've had problems with this myself. One solution (not ideal) is store
dates as a string with the month as text e.g. 01/Jul/2005. Clumsy, but
maybe get you out of a tight spot.

Alternatively, if I remember correctly, even with your locale set to
dd/mm/yyyy, if you perform the import manually, Access will default to
mm/dd/yyyy when you get to the "select data type for column" prompt
(String / Date etc.) Therefore in your VBA code you probably have to
tell access to dd/mm/yyyy - or maybe you're not pulling it into Access
but using OLE to control Excel in which case this is irrelevant.

Sorry I can't be more help.

RS200Phil wrote:
Hi,

I am here in the UK, using VBA to import an XLS file into Access.

If I open the XLS file in Excel I can change the date formats to my
heart's content and it all works beautifully. If I change the data
format to "General", for example, I get...

38544...for 11th July, and
38545...for 12th July

However, when my Access application opens the same file and uses VBA to
change the format of the cells, I get...

38663...for 11th July (i.e. 7th November), and
38693...for 12th July (i.e. 7th December)

All the program does (at the moment) is open the file, format the
relevant column and save and close the file.

I cannot find any way of reading this date value correctly. It is
stored internally as a number, I appreciate, but even the number seems
to change if I open the file using VBA.

Any suggestions would be most welcome.

TIA

Phil