vba open csv file messed date format

Question

i'm using vba from foo.xlsm file, to open bar.csv file then copy it to the foo.xlsm file. however, surprisingly, it turns out that it will mess up with the date format, sometimes recognize as mm/dd/yyyy, sometims dd/mm/yyyy.

The csv file has a row like this:

"USD/MYR","TRF:1234","20/04/2017","01/06/2017","11/09/2017","01/06/2017"

, so all the dates are in dd/mm/yyyy format.

If I open bar.csv file manually, the 4 dates are shown correctly, as

20/4/2017   1/6/2017    11/9/2017   1/6/2017

However, if from foo.xlsm using vba to open bar.csv file, it turns out to be

20/04/2017  6/1/2017    9/11/2017   6/1/2017

, and hence later copied to foo.xlsm wrong dates.

It seems excel is tring to interprete the dates as mm/dd/yyyy first, only if that doesn't work, it'll read it as dd/mm/yyyy.

The vba code is like this:

Sub import_via_excel(source_file As String, source_sheet As String, target_sheet As String)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wbThis = ActiveWorkbook
    Dim dest_sheet As Worksheet
    Set dest_sheet = wbThis.Sheets(target_sheet)

    Dim src_book As Workbook
    Set src_book = Workbooks.Open(source_file, True, True) ' Open in "ReadOnly" mode
    Dim src_sheet As Worksheet
    Set src_sheet = src_book.Sheets(source_sheet)
    src_sheet.Activate
    last_col = ActiveSheet.UsedRange.Columns.Count
    last_row = ActiveSheet.UsedRange.Rows.Count

    Dim row As Integer          ' row counter.
    Dim col As Integer          ' col counter.
    Dim src_data  As String
    For row = 1 To last_row
        For col = 1 To last_col
            src_data = src_sheet.Cells(row, col).Value
            dest_sheet.Cells(row, col).Value = src_data
        Next col
    Next row

    src_book.Close False ' close file without saving (FALSE)
    Set src_book = Nothing

    wbThis.Activate

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

What could went wrong?

PedroMVM · Accepted Answer · 2017-07-02 12:53:04Z

5

If you are using Workbooks.Open to open your CSV file, Excel will convert dates to US format, unless it is impossible (so it will try other formats).

You can try adding an argument Local:=True to solve it.

answered Jul 2, 2017 at 12:53

PedroMVM

3421 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TomServo · Accepted Answer · 2017-07-02 12:53:58Z

2

Since it seems you're right that Excel is trying to interpret the dates as mm/dd/yyyy first, only if that doesn't work, it'll read it as dd/mm/yyyy:

Use the NumberFormat property to force the proper format:

For row = 1 To last_row
    For col = 1 To last_col
        dest_sheet.Cells(row, col).Select
        ActiveCell.NumberFormat = "dd-mm-yyyy"
        src_data = src_sheet.Cells(row, col).Value
        dest_sheet.Cells(row, col).Value = src_data
    Next col
Next row

Try that and see if it helps, forcing the proper date format onto each destination cell first.

edited Jul 2, 2017 at 12:53

answered Jul 2, 2017 at 12:20

TomServo

7,4895 gold badges34 silver badges52 bronze badges

3 Comments

athos Over a year ago

But I can't know in advance what the format is, in a general function...

TomServo Over a year ago

Perhaps, but could you not supply the desired format as another parameter? Do you not know the desired format for any given input file?

TomServo Over a year ago

Are these right, if considered in dd-mm-yyyy format? "20/04/2017","01/06/2017","11/09/2017","01/06/2017" If so, then perhaps your original data source formats days-first. If those aren't correct, and those are a mix of formats, perhaps the best thing is to somehow enforce a standard format in your input, so that you can force it to be the way you want it.

Collectives™ on Stack Overflow

vba open csv file messed date format

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related