excel - Workbooks.OpenText 无法正确解析 csv 文件 Excel 2016

Question

我很确定这在以前版本的 Excel 中可以正常工作

测试文件:

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

日期和时间之间的分隔符是空格(ASCII码32)

如果文件保存为 .txt文件，OpenText 方法可以正确解析。

如果文件保存为 .csv文件，OpenText 方法似乎根本不起作用

如果将空格替换为逗号，并且文件保存为 .csv文件，OpenText 方法会将行分成两列，但不会正确解释日期字符串。

我的 Windows 区域设置是 mdy，我的 Excel 版本是 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

感谢大家的建议。在可能的解决方案中，出于我的目的，我决定删除 *.csv文件的后缀。这有效并且可以适应。 QueryTable方法也可以工作，以及 Axel 发布的警告。

如果有人感兴趣，这是适用于我的方法的代码。

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