.csv file. Different when opened manually and via VBA

**Richard Buttrey** · 01-21-2009, 07:29 PM

Hi,Can anyone suggest what's going on here with the attached file.Opening it manually the dates in column C are all consistently date numbers. i.e. using the =ISNUMBER() function to test them, they all return True
However when I include the VBA instruction:

Please Login or Register  to view this content.

to open the same file, the dates are a mixture of date numbers and what appear to be text since some return the value FALSE

However I think they are still date numbers since when I use say=Left(c10,2) to extract the '01' which is actually month 1 January, I get the result 40, which is in fact the 40 from the number 40026, which of course implies it is a date, albeit it the wrong one, i.e. 1st Aug 2009 not 8th Jan 2009.
This file originates from Quick Books if that helps and I'm using Excel 2007.My problem is that since the 'dates' are inconsistent I can't rely on being able to filter them.So.. two questions.

1. Why does the file open manually with all dates consistently proper dates, yet when opened with a macro they are different.

2. Can anyone suggest why these dates are inconsistent and how they could be manipulated to consitent date numbers.

I'm fast disappearing up my own backside with this!

Usual TIA

**Richard Buttrey** · 01-22-2009, 07:01 AM

Hi,

I've partially solved item 2 of the OP by using a combination of converting the date number to three strings containing the dd/md/yyyy, recombining these into a string but swapping the day & month over, and finally converting the modofied string back to a date number. The formula below does not directly relate to the Original workbook since I had added some intermediate columns whilst testing it.

Please Login or Register  to view this content.

However I'm still looking for a solution to Q1 if anyone can suggest anything.

**Richard Schollar** · 01-22-2009, 07:45 AM

Hi Richard

Have you tried a syntax like:

Please Login or Register  to view this content.

Richard

EDIT: This opens the dates as per US Date format as Andy said. Which is quite a pain. The FieldInfo parameter seems to be completely ignored too. If you must open the file with VBA then you could apply TextToColumns after opening it which should at lkeast allow you to convert the values back to UK Dates by specifying the import format. Not sure if doing TTC in VBA will have the same lack of impact as OpenText had though...

**Andy Pope** · 01-22-2009, 08:01 AM

When opening manually the regional settings for dates are used so D/M/Y.
When using VBA US setting are used, M/D/Y.

See this previous post on a similar issue.
http://www.excelforum.com/excel-prog...t-failure.html

**Richard Buttrey** · 01-22-2009, 09:40 AM

Thanks both Andy and Richard, I've noted the suggestions and will play around with them.

The other problem, and I guess it's not an Excel problem per se, is why the file which originates from the Quick Books system should have a mixture of US & UK dates all within the same column. I don't kniw Quick Books and have no control over the parameterising of any exported data, so will have to cater for the inconsistencies in VBA.

Regards

**Richard Schollar** · 01-22-2009, 09:52 AM

Richard

I expect QuickBooks outputs in only the one date format - it's only when you open via code into Excel that the problems start. You should be wary of accepting the'real' dates that are present after you have opened via VBA as it sounds as if these have been converted to their US equivalents (ie Day and Month transposed). The remaining Text 'dates' will be those with a Day value greater than 12.

You can confirm that the dates in the csv are OK by opening the csv file in Notepad or another similar text editor. You should find the dates are all consistent.

It's a shame that VBA doesn't do what it says on the tin (re the FieldInfo parameter not actually doing anything). Of course, you could look at it that it's cos the US just has a wonky date format too ;-)

**DonkeyOte** · 01-22-2009, 09:54 AM

From memory I think it's down to non-ambiguous dates ie wherever the day of the transaction exceeds 12 it exports as text... I've not mucked around with QB for a long time but I remember it causing a few expletives here and there... it may be down to your client regionals... ie if you run on US and export do you get the same issue ?

Edit: a reiteration of Richard's post in essence... though I suspect (begrudgingly) that he's right (again) insofar as it probably is XL-end...

Edit: Lest we forget we're talking about QB... from a data extraction perspective it made Sage look positively awesome... have they provided an official ODBC Driver as yet ? In ye olde days you had to use 3rd party QODBC which was horrendous... in fact I think I gave up on it in the end...