Date format changes dependent on VBA or regular Open File

[rogerbowering]

I have a VBA script to open a CSV file (output from Notes) and then process this file in a new Macro enabled workbook, ending with a nice pivot table.

I just recently notcied that the number of rows I'd expect to see for, say, August 2013 is far less than it should be. I then found a number of 'Dates' that appeared to be USA format, but on clicking them they say they are UK Date format. So if I filter using the drop down filter options by: [x] 2013 >> [x] 08 I get 136 records, but if I use a text fiter "08/2013" I get far more

I've included what I mean below

1. Opening the CSV file and checking the format of the date values sows them as Date Format but the DATEVALUE function throws an error for all rows

2. Opening the same CSV file via VBA code shows some date left aligned and some right aligned (and have been changed to look US format). All of them appear to have the same Date Format (*27/12/2013);but now some show a valid DATEVALUE and some don't

3. After the code has finished and checking the resultant Worksheet, the dates look the same. They all proclaim to be UK Date format and filter option now shows Dates up December 2013

I'm not sure this is a VBA problem or an Excel CSV vs Excel XLSM issue but the code I use to open the CSV file is:
Workbooks.Open filename:="C:\Lotus\Notes\framework\ClaimCareMI.csv"
and then I use standard selection copy and pastes.

Finally, the main XLSM file containing the VBA that opens and copies from the CSV file has the '1904 Date System' thing unchecked. The CSV file is a new file everytime an export from Lotus Notes happens, but looking at the current one, it, too has this function set the same

Can someone rescue me from this insanity

---

Sample	after Opening CSV using File Open
Instruction Date	Instruction Date 2	Branch Created	Branch Responsible	SIte	[Datevalue]
27/06/2013	27/06/2013	LON	SOU	LON	#VALUE!
21/06/2013	21/06/2013	LON	SOU	LON	#VALUE!
26/02/2013	26/02/2013	LON	SOU	LON	#VALUE!
09/05/2013	09/05/2013	LON	SOU	LON	#VALUE!
08/05/2013	08/05/2013	LON	SOU	LON	#VALUE!
08/05/2013	08/05/2013	LON	SOU	LON	#VALUE!
24/04/2012	24/04/2012	LON	SOU	LON	#VALUE!
24/04/2012	24/04/2012	LON	SOU	LON	#VALUE!
Sample	after Opening CSV using VBA
Instruction Date	Instruction Date 2	Branch Created	Branch Responsible	SIte	[Datevalue]
27/06/2013	27/06/2013	LON	SOU	LON	41452
21/06/2013	21/06/2013	LON	SOU	LON	41446
26/02/2013	26/02/2013	LON	SOU	LON	41331
05/09/2013	05/09/2013	LON	SOU	LON	#VALUE!
05/08/2013	05/08/2013	LON	SOU	LON	#VALUE!
05/08/2013	05/08/2013	LON	SOU	LON	#VALUE!
24/04/2012	24/04/2012	LON	SOU	LON	41023
24/04/2012	24/04/2012	LON	SOU	LON	41023
Sample	From Final Workbook after VBA has run
Instruction Date 2	Instruction Date 2	Branch Created	Branch Responsible	SON
27/06/2013	27/06/2013	LON	SOU	LON	41452
21/06/2013	21/06/2013	LON	SOU	LON	41446
26/02/2013	26/02/2013	LON	SOU	LON	41331
05/09/2013	05/09/2013	LON	SOU	LON	#VALUE!
05/08/2013	05/08/2013	LON	SOU	LON	#VALUE!
24/04/2012	24/04/2012	LON	SOU	LON	41023

<tbody>
</tbody>

A

Sample

after Oening CSV using File Open

<tbody>
</tbody>