CSV import casuing Day & Month Swapping around when day is less than 13

[JonRowland]

Hi ppl,

Same problem http://www.mrexcel.com/forum/showthread.php?t=560649&highlight=IMPORT+CSV+DATE although I'm using VBA to import CSV file into a Template document.

The CSV contains dates formatted as dd/mm/yyyy hh:mm:ss and when the csv data is copied into my template file the date changes to mm/dd/yyyy hh:mm:ss when the day is less than 13. This is causing me a headache now and wonder how I can ensure that the dates are copied in as expected.

My import code is
<code>
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Workbooks.Open Filename:= _
"H:\Process\Temp\Template.xlsm" ' This is a blank file used for
' the purpose of importing souce data pre-processing


MyPath = "H:\Process" ' change to suit
Set wbDst = Workbooks("Template.xlsm")

strFilename = Dir(MyPath & "\*.csv", vbNormal)

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Loop

' Deletes original sheet in Template.xlsx which isn't needed
wbDst.Worksheets(1).Delete
</code>

Any ideas plseeeee.

Thx
Jon

 

[jmthompson]

I'd recommend recording a macro in a new workbook to open the file and format the column in question as date.

Then replace the <code>Workbooks.Open(Filename:=MyPath & "\" & strFilename) portion of your code with the Workbooks.OpenText code.
</code>

 

[JonRowland]

Won't this imply that the date fields are always in the same place?

I forgot to say that I am opening more than 1 CSV & each looks different.

What I don't get is why if I open by double clicking there isn't a problem but via VBA there is.

Jon

 

[James_B]

That sounds really annoying. Seems like you need some way of telling Excel what kind of things it's meant to be importing. The first thing that comes to mind is storing the data in a database (Access?) where you can assign each column a datatype. Is this possible in your case?

 

[JonRowland]

Hi James,

Access isn't possible. Excel or bust

 

[dk]

Hi

You should be able to get around this by setting the "Local" argument to true e.g.

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename, Local:=True)

There seems to be a lack of information regarding this argument in the help. The online help for 2007 shows this description of the Local argument but for the OpenText method:

Specify True if regional settings of the machine should be used for separators, numbers and data formatting.

Based on my testing (2003 and 2010) it appears as though is also how the Local argument works with the Open method.


HTH
DK

 

[JonRowland]

Dk,

All I can say is yahooooooo. Works a treat from the tests I've done.

Cheers bud