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 allIf 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
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.
CSV
andText
are really not the same forExcel
. Not only that the delimiter settings are very special forCSV
and are not setable using a parameter inWorkbooks.OpenText
. Also other parameters like field types (FieldInfo
) will also not be respected while openingCSV
files. And also the unicode handling is a very special case forCSV
and is signly different fromText
.You could try using
QueryTables
like so:But using QueryTables of course you must be careful not adding them multiple times without necessary but refreshing them instead or first deleting them and then adding them again.
It seems like this might be the same issue addressed in this thread:
Opening CSV files in Excel 2016