Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHANGE TEXT DATE TO NUMERIC DATE | Excel Worksheet Functions | |||
how do i change text format date to date (i.e., mm/yy to mm/dd/yyy | Excel Discussion (Misc queries) | |||
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! | Excel Worksheet Functions | |||
Change text color of specific date range by macro in Excel | Excel Programming | |||
Change text to date and check against date in cell | Excel Programming |