what is actually in the CSV file? open it in Notepad as Excel cleverly tries
to interpret the data


"StuartBisset" wrote in message
...
Hi Folks

I have written a macro that grabs data from a seperate CSV file and
puts it into my workbook. The CSV file is a download from another
system and there was an issue with some dates coming out in numeric
format and some coming out in text format. I got round this by
using ...

DateValue(wkbCSV.cells(xLoop,1))*1 (where "wbkCSV" is the CSV file
and "xLoop" cycles thru all the rows)

... to put the date integers into my array.

However the CSV file shows 01/04/2009 (ie 1st April, integer 39904)
but when I spit the array out onto the worksheet it shows as
04/01/2009 (ie 4th Jan, integer 39817).

There is not a problem with my regional settings in control panel (set
to dd/mm/yyyy) or with the number format in excel (also set to dd/mm/
yyyy).

When I select the date cell in the csv file and I go to the immediate
window in the VBE and type ....

? DateValue(selection)*1

... it returns 39904 (correct).

But when I run my macro it seems to grab 39817 instead. How can this
be?

Any help would be much appreciated.

Cheers

Stuart