Date format converts when I open CSV with VBA

[djburnheim]

When I open a CSV file using the code below the dates in the CSV which are in the format dd/mm/yyyy change to mm/dd/yyyy. If I open the CSV file through explorer it's all fine. Any ideads why this would happen???

***code***
Summary = "J:\Tsdshared\CallCentre\Weekly stats\Summary*.csv"
Queue = "J:\Tsdshared\CallCentre\Weekly stats\Queue*.csv"

Workbooks.OpenText FileName:=Queue, DataType:=xlDelimited, Comma:=True
Sheets(1).Select
Sheets(1).Move After:=Workbooks("Daily Stats.xls&quot.Sheets(1)

Workbooks.OpenText FileName:=Summary, DataType:=xlDelimited, Comma:=True
Sheets(1).Select
Sheets(1).Move After:=Workbooks("Daily Stats.xls&quot.Sheets(2)

'Match date in report sheet with column in Daily Stats
ReportDate = Format(Sheets(1).Cells(5, 2), "dd-MMM&quot

With Workbooks("Daily Stats.xls&quot.Sheets(1).Range("a1:Q40&quot
Set dCell = .Find(ReportDate)
If Not dCell Is Nothing Then
dColumn = dCell.Column
dRow = dCell.Row
End If
End With

 

[RobBroekhuis]

The procedure used when opening through the explorer is equivalent to workbooks.open, not workbooks.opentext. Try using workbooks.open, and see if it solves your problem.
Rob

 

[djburnheim]

Rob I tried using workbooks.open but it still had the same effect. Also if I use workbooks.open I have to have the full filename...the wildcard (*) doesn't work. I've checked all the regional settings on everything and I just can't see what's making it do this.

Dave

 

[djburnheim]

If I open the CSV file then save it in xls format it works but I'd prefer not to have to do that

 

[RobBroekhuis]

Why not open the file and copy the sheets to your compilation, as you already do? I don't understand why you'd have to save as .xls.
Rob

 

[djburnheim]

The original problem is still the issue...if I use any sort of VBA to open the CSV file the dates get messed up. I thought perhaps it was something to do with the way the CSV file is generated but I tried creating a new workbook in excel saving it as CSV file and I get the same problem????

 

[bryanbayfield]

Have you tried using the macro recorder, then opening the file, whilst making sure that you specify the correct column format for your dates after selecting "import as delimited" in the dialogue box that comes up?

The code that that generates might give you a clue.

But to be frank, Excel, VBA and dates combined can be a freaking nightmare. The data seems to take on a life of it's own when it's got dates in it.

 

[nath]

Hi,

Can you give me some precision:
1. When you open through explorer you say it's OK. Do you open it with Notepad or with Excel. Is the format right when you open with notepad?
2. Did you try to transpose your calling procedure in a fresh workbook (just to test). Sometimes sheets get corrupted and the formatting get stuck in strange ways.

Nath

 

[djburnheim]

Through explore I've tried opening in both notepad and Excel and format is all fine (02/04/2003) then if I open it using VBA it changes to 04/02/2003. I just tried with a new workbook but same thing happened.

 

[SmallCraig]

Greetings to you all,

Just thought I'd weigh in (and my weight is not inconsiderable!), not with a solution, but with a further occurrence of the same problem!

I have experienced exactly the same problem as you, dj, and have also been perplexed that, when I open the same CSV file via the Excel Open File ... dialogue, the dates are in the proper format! As an aside, the question must be asked - who was the lunatic who thought putting dates in month/day/year format would be a good idea in the first place? A notable anomoly in this blanket "conversion" is this - if the date cannot logically be reformatteded, i.e., the MONTH would end up > 12, the date remains in the original format!

The only way I've managed to get around this problem is to manipulate the dates once I get the file onto a platform that doesn't want to play God with the date format! However, this solution is not foolproof, and so I also would like to be able to find a satisfactory solution to this irritating problem.

Best regards,
SmallCraig

 

[bryanbayfield]

DJBurnheim, have you tried using the macro recorder as per my previous post?

 

[djburnheim]

Bryan, Sorry I had skipped over your first post for some reason. I've tried what you've suggested and think I can see how to do it now but I can't seem to make it work. I have added FieldInfo parameter but it still does the same thing. Assuming the date column is Column B in the CSV sheet this is what I have:

Dim FileName, Summary, Queue As String

Summary = "J:\Tsdshared\CallCentre\Weekly stats\Summary*.csv"
Queue = "J:\Tsdshared\CallCentre\Weekly stats\Queue*.csv"

Workbooks.OpenText FileName:=Queue, DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(2, 4)
Sheets(1).Select
Sheets(1).Move After:=Workbooks("Daily Stats.xls&quot.Sheets(1)

Workbooks.OpenText FileName:=Summary, DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(2, 4)
Sheets(1).Select
Sheets(1).Move After:=Workbooks("Daily Stats.xls&quot.Sheets(2)

 

[bryanbayfield]

How strange - as I say Excel, VBA and dates don't really go together.

I'm a little flummoxed as to what to suggest. One thing to try (not saying it will work) is to use format 2 (ie, text) instead of 4 for your import. Hopefully that will import your dates exactly as they are in the csv file but obviously Excel will not recognise them as dates. What then _should_ work is to do a replace of "/" with "/" - for some reason this tends to work, although I've always done it using the user interface rather than through VBA.

One last query would be: are your regional settings set to "dd/mm/yy" in your control panel. I'm guessing yes but I might as well ask!

 

[FunkMaster]

I thought i throw in me pennies worth ( about 2p).

Yes i suffer the same problem its with the dates from and text file to an excel spreadsheet and this using vb, it does it for some but not all ( i will try and look into that) but yeah it also does it for numbers, we have an 13 digit number which you have to change the format to number to get the actuall number ( if not it shows as 11to power of 10 squared dodie) so when go through all the script in the end its 1100000000 which is just wrong.

Anyway the point of this we have an manual process which is copy format the date, copy the data to notepad then change the format to text and copy the data back again. i have no idea if this helps.


Cheers

wayne 5% is not an rise, its an poke in the eye.

 

[Codenexus]

I was also encountering this issue with Excel VBA & date formatting. It seems even when I specify in the FieldInfo parameter that I want a column formatted as text, Excel still formats some cells as dates, e.g.:

workbooks.opentext filename:=strFileName, _
...
FieldInfo:=Array(Array(1, xlColumnDataType.xlTextFormat))

Very frustrating; however I did discover a workaround by recording a macro. The VBA code I got:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\example.CSV" _
, Destination:=Range("A1&quot)
.Name = "example"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With

Note the parameter TextFileColumnDataTypes. To record this macro (from Excel 2000), select from the menu:
Data -> Get External Data -> Import Text File

However I'd prefer to use the OpenText method, so if anyone knows the secret of this problem, I'd like to hear from you.

Thanks,

Mike

 

[gravec]

Hi,

Further to SmallCraig's note, Excel makes a complete hash of dates where the "dd" portion of the date is > 12.
As it can't validate it as a valid month, it converts the whole cell to text. You then end up with a combination of reversed dates and text

One point, the Workbooks.Open does work with Excel 97!

Little use to me as much of the other functionality in my macro is new to Excel 2000.

If anybody comes up with a "fix" for this issue, I would be very interested.

Best regards
Chris

 

[gravec]

I have researched this some more and found a sort of fix - store the dates as mm/dd/yyyy in your CSV file and they get converted (correctly) to dd/mm/yyyy.

Admittedly it's a bit of a cheat, but it works.

All the best
Chris

 

[LFB]

Hi All,
I found that if I import the data using the external data import it seems to work ok.

Cheers.