Apparently you did not select 'TEXT' as the field type for the column which
is getting imported as DATE... I assume you have that option in import
through Query. I tested with importing a text file and got the following
recorded macro...
'-------------------
Workbooks.OpenText Filename:= _
"C:\Test.txt", Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2,
2), Array( _
3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True

End Sub
'-------------------
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Alan" wrote:



"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com"
wrote in message
...
Two suggestions;

1. Record a macro while importing the file and later edit that macro
You will see an entry like following -
FieldInfo:= Array(
Array(0,1),
Array(14, 1),
Array(39, 9),
Array(48, 1),
Array(54, 1),
Array(60, 1),
Array(72, 1),
Array(85, 1),
Array(93, 1),
Array(100, 1)
)
Try to identify the column which is being treated as date and change
1 to 2
in that array entry... this should solve text getting imported as
date
problem.


Hi Sheelo,

I recorded the macro and this is what I got:

+-+-+-+-+-+-+-+-+-+-+-+-+

Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:="TEXT;file .txt",
Destination:=Range("$A$1"))
.Name = "importfile"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


+-+-+-+-+-+-+-+-+-+-+-+-+

The Array(1) bit is different to what you posted (you have two
parameters)?

Does that make any sense?


2. Do you really get the file opened perfectly? I would guess that
ASCII 13
would take the data to next row... Yes, you can replace it with a
space
character... ASCII 10 is treated as End of Record while importing a
text
file...

The ASCII 13 imports as a small 'square' character (when I described
that it opened perfectly). I guess I was overstating it a bit, but
the 'square' rather than a line break (CR) within a cell is not an
issue for me, so it is relatively perfect ;-)

If I was going to use VBA to edit the file prior to importing, how
would I do that though?

Thanks for your assistance,

--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb