Opentext method

[imported_unknown]

Hello.
I'm trying to use opentext method in order to open a csv file containing a date column.

I started recording a macro to do this then opened the file and specified DMY datatype for the date column. the file is opened and everything is ok. i stop the recording.

I go into the macro and see the opentext methond with all it's arguments. i run the macro, the file is opened but this time without the correct datatype in the date column. despite the fact that the correct opentext arguments are correct.
as a result day and month in some of the date values are interchanged.
for example if i have a date 1/10/02 in the csv file, when using opentext 10/01/02 is displayed instead.

i'd appriciate any help in this

 

[Andrew Poulsom]

I have a similar problem.

If I turn on the macro recorder and format a cell as "dd/mm/yyyy" Excel records it as ""m/d/yyyy". But if I run the macro on another cell it formats it as "dd/mm/yyyy".

That said, I do not have a problem opening the OP's CSV file.

 

[imported_unknown]

After opening the CSV file using a macro i can change the date formatting but it will have no effect on the value itself.
Also it is worthwhile to mention that not all dates are transposed.
I suspect that only dates that have days with 1 digit and month with 2 digits suffer this phenomena

 

[nehpets12]

Try This Code It works for me this is the bit that changes date format Array(5, 4),



'Workbooks.OpenText Filename:="Enter File Name And Location", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 4), Array(6, 1), Array(7, 1))
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

 

[imported_unknown]

Hello nehpets12

I tried as you suggested in your last reply to adjust the array arguments and i'm afraid it still doesn't work and the problem remains unsolved, perhaps you can check the lines i posted.

For now it seems there is no solution but to use the open dialog box method from the macro.

If anyone knows how to solve this problem please let me know.

Rafael

 

[Andrew Poulsom]

I think I have found a workaround for your/our problem.

Sub Test()
    Dim FileName As String
    Dim FileNum As Integer
    Dim r As Long
    Dim Data As String
    Dim c As Range
'   *** Change path and file name to suit ***
    FileName = "P:TEMPMrExcelCSVDates.csv"
    FileNum = FreeFile
    r = 1
    Workbooks.Add
    Open FileName For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, Data
        ActiveSheet.Cells(r, 1) = Data
        r = r + 1
    Wend
    With Columns(1)
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True, _
        FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2))
    End With
    For Each c In Range("E2:E" & Range("E65536").End(xlUp).Row)
        c.Value = CLng(DateValue(c.Text))
        c.NumberFormat = "dd/mm/yyyy"
    Next c
    With Range("F2:F" & Range("F65536").End(xlUp).Row)
        .NumberFormat = "hh:mm"
    End With
End Sub

Edited for missing code tag.

This reads in the CSV file line by line then uses Text to Columns to parse the data. The key seems to be setting all the columns up to and including the date to DataType 2 (Text). So this method will probably also work with the OpenText method - I will leave you to try it.
This message was edited by Andrew Poulsom on 2002-10-08 06:13

 

[Rafael44]

Hellow andrew
Using the opentext with an argument to parse the date column as text and then in the macro changing the numbering format to dmy solved the problem

Thank you very much

Rafael

 

[Lubo]

Hi,

Andrew's code worked great for me too. However, one thing to be aware of ... you need to autofit the columns after TextToColumms. Otherwise c.text is ######## and you get an error on DateValue.

Many thanks for this. I will be able to use it in many apps.

Lubo

 

[Legacy 98055]

Excellent Andrew!
Tom