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
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
It seems like this might be the same issue addressed in this thread:
Opening CSV files in Excel 2016
CSV files are Character Separated Value files, not Comma separated.
For more than half the world the separator character is semicolon (;),
not a comma (,)
Excel 2016 properly respects your Windows regional settings, and uses
the specified "List separator" character
One solution is to change your regional settings for the "List
separator" attribute to the character you want Excel to default to
using, e.g. a comma (,)
This can be changed in:
Control Panel / Region / Additional Settings / List separator:
CSV
and Text
are really not the same for Excel
. Not only that the delimiter settings are very special for CSV
and are not setable using a parameter in Workbooks.OpenText
. Also other parameters like field types (FieldInfo
) will also not be respected while opening CSV
files. And also the unicode handling is a very special case for CSV
and is signly different from Text
.
You could try using QueryTables
like so:
Sub foo1()
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
Set WB = Workbooks.Add
With WB.Worksheets(1).QueryTables.Add(Connection:= _
"TEXT;" & sFN & "", Destination:=Range("$A$1"))
.Name = "test"
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(xlDMYFormat, xlGeneralFormat)
.Refresh BackgroundQuery:=False
End With
End Sub
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.