OOTHUB Technologies

Excel VBA Workbooks.OpenText, just can't get date formatting to work

Asked 3 days ago Modified today Viewed 222 times Asked By Vineeth Kumar B

I have .csv file with this...

name,package,start,end,state
Bobby the guru,Some text,08/04/2024,08/09/2024,Lapsed

and I'm opening it with

Function GetData()
Workbooks.OpenText filename:="c:\MemberHistory.csv", _
    FieldInfo:=Array(Array(3, xlDMYFormat), Array(4, xlDMYFormat)), _
    DataType:=xlDelimited, Comma:=True, TextQualifier:=xlTextQualifierNone, Origin:=xlWindows, StartRow:=2

It works but the data in columns 3 and 4 are 4/8/2024 and 9/8/2024 when formatted as date("14/03/2012"). I'm expecting 8/4/2024 and 8/9/2024.

Can anyone suggest where I'm going wrong please?

Answers

I'd suggest to rename your file resp. use the ending txt instead of csv. In such a way you should avoid Excel's automatic conversion routines for CSV files which I guess apply before the information in Fieldinfo is considered

Use the following function

Function GetDataA()
' xlDMYFormat =4
' xlGeneralFormat=1

    Workbooks.OpenText Filename:="c:\MemberHistory.txt", _
        DataType:=xlDelimited, _
        TextQualifier:=xlNone, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, Comma:=True, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 4), Array(4, 4), Array(5, 1))

End Function

Some reading

Workbooks.OpenText ignoring FieldInfo column parameter

Workbooks.OpenText not parsing csv files properly Excel 2016

Answered by Rahul Sharma 3 days ago