Text file import using ADO - XL converts fields to dates

[Guest]

I am using Office 2003 on Windows XP with ADO 2.8.

I am using ADO to import the contents of a text file into several Excel
worksheets.
The program works great, BUT one field is a number separated by a forward
slash followed by another number. Examples: 12/0, 11/2, 24/5, 15/60.

For most rows (there around 128,000+), this field is imported correctly. For
several others, Excel converts them to dates and once converted, the original
values are lost. The raw text file is comma delimited and all fields are
surrounded by double quotes.

This behaviour occurs even when I import the first 65,536 rows using the
built-in parser when opening the text file manually. BEFORE import I can see
that the fields are correctly displayed, AFTER import they are changed - even
if I set all cells in the destination sheet to text type fields.

How can I fix this problem? I really prefer to use ADO if at all possible
since I have a whole program already dedicated to this project. Thanks much
in advance for your input.

 

[Guest]

The .Value property of an ADO Field is a variant data type. If you are not
doing so, I would suggest first that you explicity reference the .Value, for
example:
Range("A1").Value = MyRs.Fields(2).Value
instead of
Range("A1").Value = MyRs.Fields(2)

Then, to help it be recognized as text and not as a date, it might help to
assign it to a string variable:
Dim StrVar as String
StrVar = MyRs.Fields(2).Value
Range("A1").Value = StrVar

I haven't tested this so can't say if it will solve the problem, but hope it
might help.