PCTech Forums

We have a VB script which is designed to import data from a CSV file into an excel spreadsheet automatically..

the problem is this.. the dates in the CSV file are stored in UK format.. dd/mm/yyyy.. however.. when we import it using VB, it reads them as US dates.. mm/dd/yyyy.. this obviously means that any dates past the 12th of the month are not read correctly.. and also.. once the xls is mailed to the recipients, depending on their regional settings.. dates acn look really screwed up.

is there any way to tell VB/Excel to look at the dates as UK format rather than US??

changing regional settings only affects the way the dates are displayed.. it does NOT affect the way they are read from the csv..

please help.. I spent half a day yesterday working out how to screw this up in a completely different way to how it was originally screwed up..

Or... alternatively.. if anyone knows how to use VB to tell Excel to format a cell with a date in it as a general format.. or text format cell.. that would be workable too

I don't know if this is any use to you, but I just used notepad to create a file containing '21/06/04' and saved it as temp.csv. When I opened it in Excel 2003 it was correctly read & displayed as 21/06/04, so it certainly seems as thought some excel setting at your end is not correct.

When you format the cells as date format, you have the option of choosing american or british locales. Is there a way of setting the default to english here?

yes Restek.. if I import the file manually into Excel.. it works fine.. however the problem comes when using VB to import it.. VB uses US conventions by default..

I got around this by creating a totally new macro for the import of the file and replacing the original code that the original author had ripped from vb-helper..

excel_app.workbooks.open _
filename:=txtFromFile.Text, _
Format:=xlCSV, _
Delimiter:=",", _
ReadOnly:=True

if you look at http://vb-helper.com/howto_csv_to_excel2.html you will find the full code.. run this on your CSV.. and watch the dates change..

Have managed to get it sort of working.. except that it randomly falls over now..