Excel Change Text to Date

[kahoar]

I am opening a comma-delimited CSV file using:

Workbooks.OpenText FileName:="InFile.csv", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
IsText), _
Array(2, IsText), Array(3, IsText), Array(4, IsText), Array(5, IsText), _
Array(6, IsText), Array(7, IsText), Array(8, IsText), Array(9,
IsText)), _
TrailingMinusNumbers:=True

One row has the text "5-10" in column H. No matter what I do Excel is
determined to convert that cell to "10-May" and it changes the datatype for
that cell to "Date".

I would hate to have to "dump" Excel and go to pire VB for this project.

Anyone have any ideas how to stop Excel from converting text to date?

 

[kahoar]

I found my own answer.

I simply changed the extension from CSV to TXT. Now everything is working
correctly.

Excel can certainly be STUPID at times.

 

[Gary''s Student]

Say we have a file:
C:\test folder\x.csv
with records like:
1-1,2-2,3-3,4-4,5-5,6-6,7-7,8-8,9-9,10-10,11-11,12-12

Using the Macro Recorder:

Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\test
folder\x.csv", _
Destination:=Range("A1"))
.Name = "x"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

resulted in no date conversion.

 

[paulgu]

I've encountered exactly the same problem. However there is a simple solution.
Save the .csv file as an Excel file and work with the Excel file. You'll find that the dates then come out correctly.
HTH
Paul