Data from .txt file being read in as date, and should be text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

missycoo@yahoo.com - 26 Jul 2007 16:17 GMT

I have a macro that reads in data from a .txt file. The data contains
user IDs that consist of the first 4 letters of their name and 4
numbers. When names are read in that begin with MARCxxxx (ie
MARC1234), excel is formating them as a date, and I lose the original
data (even when I try to format them after the import, they are a
number and the letters are not there. I've tried to format the column
that they data read into before the import, but excel still imports
them as a date. How can I make the import be read as text?

Here is the code for the section I am reading in data for.

Do While (EOF(Filenum) = False)
Line Input #Filenum, CurrentLine
If CurrentLine = "" Then 'skip line if there is nothing on it
Else
Cells(rownum, 3) = Mid(CurrentLine, 1, 12)
Cells(rownum, 1) = Mid(CurrentLine, 13, 15) ' This line
reads in as date when user is MARCxxxx
Cells(rownum, 2) = Mid(CurrentLine, 28, 34)

Reply to this Message

Dave Peterson - 26 Jul 2007 17:39 GMT

replace that single troublesome line with:

with cells(rownum,1)
.numberformat = "@" 'text
.value = mid(CurrentLine, 13, 15)
end with

It's not actually the reading part that's causing the trouble. It's when you
plop the value back into the worksheet cell.

> I have a macro that reads in data from a .txt file. The data contains
> user IDs that consist of the first 4 letters of their name and 4
[quoted text clipped - 15 lines]
> reads in as date when user is MARCxxxx
> Cells(rownum, 2) = Mid(CurrentLine, 28, 34)

Signature

Dave Peterson

Reply to this Message

Rate this thread: