S
SysMod
I have a CSV file where the text elements in quotes have embedded linefeeds
123,"12/08/2009","is a new{LF}line",""
where {LF} is an actual linefeed, char(10)
I can open it directly in Excel 2007, which ignores these line breaks.
However, the US m/d/y dates come out incorrectly because my locale uses d/m/y
But if I open it in VBA with a Text Query (to parse the m/d/y date formats)
it does not import into Excel correctly. The text columns have linefeeds
inserted that force a newline so the line is correct up to the LF then the
record continues on the next row, eg
123 08.12.2009 is a new
line
Is there some Querytable parameter I can use to tell Excel to ignore
linefeeds?
TIA
Patrick
<pre>
Set qt = ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFileName,
Destination:=ActiveSheet.Range("A1"))
With qt
.Name = sName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001 ' Unicode UTF-8, not Windows ANSI = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = aDataTypes ' eg Array(1, 1, 1, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
DoEvents: qt.Delete ' remove querytable link when done importing
</pre>
123,"12/08/2009","is a new{LF}line",""
where {LF} is an actual linefeed, char(10)
I can open it directly in Excel 2007, which ignores these line breaks.
However, the US m/d/y dates come out incorrectly because my locale uses d/m/y
But if I open it in VBA with a Text Query (to parse the m/d/y date formats)
it does not import into Excel correctly. The text columns have linefeeds
inserted that force a newline so the line is correct up to the LF then the
record continues on the next row, eg
123 08.12.2009 is a new
line
Is there some Querytable parameter I can use to tell Excel to ignore
linefeeds?
TIA
Patrick
<pre>
Set qt = ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFileName,
Destination:=ActiveSheet.Range("A1"))
With qt
.Name = sName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001 ' Unicode UTF-8, not Windows ANSI = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = aDataTypes ' eg Array(1, 1, 1, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
DoEvents: qt.Delete ' remove querytable link when done importing
</pre>