VBA Date formatting and locale

[RS200Phil]

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

 

[Gareth]

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]

Thanks for the ideas, Gareth.

We are importing data from about 50 different clients and unfortunately,
do not have control over the date formatting on their systems. This is
the only client who sends the field as a date. All the others either
send it as a text field (as you suggested and I have requested) or in
CSV format (much preferred!).

I'm using the "Transferspreadsheet" method in the VBA code to transfer
the data into a holding table. I then carry out a little pre-processing
before INSERTING data from this table into the "live" destination.

I have tried importing the field into text field (wrong dates), a date
field (ditto) or a numeric (data type conversion failure). I had
planned to use the numeric to perform my own date-offsetting!!!

So basically, I'm still a bit low on ideas.

Thanks again for your help, though. Good advice.

Cheers

Phil

 

[Guest]

Phil
This is a known issue, plenty of people (me included) have been caught out
with it.
Try checking MSDN

I cant even remember how we got around it I'm afraid, but I think that
assuming US dates may get around it. I think maybe VBA or Access (or maybe
Excel!) is stuck in US locale for dates or something.

This fella may help
http://allenbrowne.com/ser-36.html



cheers
Simon