vba - Workbooks.OpenText not parsing csv files properly Excel 2016

Question

I'm pretty sure this worked properly in previous versions of Excel

Test File:

d/mm/yyyy hh:mm:ss
5/12/1999 6:01:12
30/11/2001 5:00:00

And the delimiter between the date and the time is a Space (ASCII code 32)

• If the file is saved as a .txt file, the OpenText method parses properly.

• If the file is saved as a .csv file, the OpenText method doesn't seem to work at all

• If the spaces are replaced with commas, and the file is saved as a .csv file, the OpenText method will split the lines into two columns, but will not properly interpret the date string.

My Windows Regional Settings are mdy and my Excel version is 2016

---

Option Explicit
Sub foo()
    Dim WB As Workbook
    Dim sFN As String
    Dim FD As FileDialog

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

Workbooks.OpenText Filename:=sFN, DataType:=xlDelimited, origin:=437, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
        FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlGeneralFormat))


Set WB = ActiveWorkbook

End Sub

---

Answer 1

Thanks to all for suggestions. Amongst the possible solutions, I decided, for my purposes, to remove the *.csv suffix from the file. This works and can be adaptable. QueryTable method would also work, along with the caveats posted by Axel.

Here is code that works for my method, if anyone is interested.

---

Option Explicit
Sub foo()
    Dim WB As Workbook, wbCSV As Workbook, swbCSV As String
    Dim sFN As String, sCopyFN
    Dim FD As FileDialog

Set WB = ThisWorkbook
Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Filters.Add "Text or CSV", "*.txt, *.csv", 1
    .Show
    sFN = .SelectedItems(1)
End With

'If CSV, remove suffix
sCopyFN = ""
If sFN Like "*.csv" Then
    sCopyFN = Left(sFN, Len(sFN) - 4)
    FileCopy sFN, sCopyFN
    sFN = sCopyFN
End If

Workbooks.OpenText Filename:=sFN, DataType:=xlDelimited, origin:=437, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
        FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlGeneralFormat))

Set wbCSV = ActiveWorkbook

'Get path as string since it will not be available after closing the file
swbCSV = wbCSV.FullName

'Move the data into this workbook
Dim rCopy As Range, rDest As Range

With WB.Worksheets("sheet1")
    Set rDest = .Cells(.Rows.Count, 1).End(xlUp)
End With
Set rCopy = wbCSV.Sheets(1).UsedRange

rCopy.Copy rDest

'must close the file before deleting it
wbCSV.Close False
Kill swbCSV

End Sub

---