Excel Change Text to Date
 

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?

Excel Change Text to Date
 

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.



"kahoar" wrote:

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?

Excel Change Text to Date
 

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:\t est
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.
--
Gary''s Student - gsnu200832


"kahoar" wrote:

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?