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

---

[TABLE="width: 532"]
<tbody>[TR]
[TD]Sample
[/TD]
[TD="colspan: 2"]after Opening CSV using File Open
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Instruction Date
[/TD]
[TD]Instruction Date 2
[/TD]
[TD]Branch Created
[/TD]
[TD]Branch Responsible
[/TD]
[TD]SIte
[/TD]
[TD][Datevalue]
[/TD]
[/TR]
[TR]
[TD="align: right"]27/06/2013
[/TD]
[TD="align: right"]27/06/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2013
[/TD]
[TD="align: right"]21/06/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2013
[/TD]
[TD="align: right"]26/02/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]09/05/2013
[/TD]
[TD="align: right"]09/05/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]08/05/2013
[/TD]
[TD="align: right"]08/05/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]08/05/2013
[/TD]
[TD="align: right"]08/05/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]24/04/2012
[/TD]
[TD="align: right"]24/04/2012
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]24/04/2012
[/TD]
[TD="align: right"]24/04/2012
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample
[/TD]
[TD]after Opening CSV using VBA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Instruction Date
[/TD]
[TD]Instruction Date 2
[/TD]
[TD]Branch Created
[/TD]
[TD]Branch Responsible
[/TD]
[TD]SIte
[/TD]
[TD][Datevalue]
[/TD]
[/TR]
[TR]
[TD]27/06/2013
[/TD]
[TD]27/06/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41452
[/TD]
[/TR]
[TR]
[TD]21/06/2013
[/TD]
[TD]21/06/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41446
[/TD]
[/TR]
[TR]
[TD]26/02/2013
[/TD]
[TD]26/02/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41331
[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2013
[/TD]
[TD="align: right"]05/09/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]05/08/2013
[/TD]
[TD="align: right"]05/08/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="align: right"]05/08/2013
[/TD]
[TD="align: right"]05/08/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD]24/04/2012
[/TD]
[TD]24/04/2012
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41023
[/TD]
[/TR]
[TR]
[TD]24/04/2012
[/TD]
[TD]24/04/2012
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41023
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample
[/TD]
[TD="colspan: 2"]From Final Workbook after VBA has run
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Instruction Date 2
[/TD]
[TD]Instruction Date 2
[/TD]
[TD]Branch Created
[/TD]
[TD]Branch Responsible
[/TD]
[TD]SON
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27/06/2013
[/TD]
[TD]27/06/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41452
[/TD]
[/TR]
[TR]
[TD]21/06/2013
[/TD]
[TD]21/06/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41446
[/TD]
[/TR]
[TR]
[TD]26/02/2013
[/TD]
[TD]26/02/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41331
[/TD]
[/TR]
[TR]
[TD]05/09/2013
[/TD]
[TD]05/09/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD]05/08/2013
[/TD]
[TD]05/08/2013
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD]24/04/2012
[/TD]
[TD]24/04/2012
[/TD]
[TD]LON
[/TD]
[TD]SOU
[/TD]
[TD]LON
[/TD]
[TD="align: right"]41023
[/TD]
[/TR]
</tbody>[/TABLE]



A[TABLE="width: 276"]
<tbody>[TR]
[TD]Sample
[/TD]
[TD]after Oening CSV using File Open
[/TD]
[/TR]
</tbody>[/TABLE]