Open text file as text not numbers

[barnabel]

I posted this on the general board but nobody has an idea if there is a
solution (though I got some suggestions)

If you have a text file that is TAB delimited, you can right click it and
say open with Excel and it will open into multiple columns.

If a field needs to contain the field separator charactor you can put
quotes around the field.

Is there anyway to indicate in the text file that a field should be
interpreted as text even when it looks like numbers?

The field contains HEX addresses (ie 00241200, 00A41323) Excel interpets
the first as an integer and removes leading 0s and the second as a string.

I have tried putting quootes around them, adding a leading and/or trailing
space, putting a single quote as the first character (this works except the
quote is displayed)

I am about to the point where I will give up and train the user to alway use
the text import wizard and set the column to text. I could write them a
macro that does the right thing to open the file but that seems to be
overkill. I am hoping that there is something that can be put in the text
file that will get Excel to interpret it right. Any other idea?

TIA
Peter Richardson

 

[Joel]

I'm a genious. I solve your problem.

You need to use a workbookOpen event placed inside a personal.xls file. The
macro will format the worksheet to text before the data is placed inside the
cells. Personal.xls is a customize file which is run every time you open a
workbook.

Steps
1) do a search on you hard drive for the directory XLSTART on my computer it
was in the following directory:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
2) In this directory creatte a new workbook called personal.xls or modify
the one that exists.
3) Open the VBA editor and select ThisWorkbook in the VBA project window.
4) Cut and Paste the macro below in the page. The macro formats the
worksheet to text.

I had to be careful not to have the macro change a workbook that already
contained data so I do a check to make sure worksheet is empty before I
reformat the entire sheet.


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Cells.Select
Set c = Selection.Find("*")
If c Is Nothing Then
Selection.NumberFormat = "@"
End If
End Sub

 

[barnabel]

Thanks Joel. I know you are a genious. I read a lot of your posts.

Since I don't know who might be opening these I am leary of putting somthing
in the personal.xls. I was hoping that there was some character to put in
the file that would solve the problem without writing code. If I have to
write code I'll give them something that lets them pick the file and do the
import.

Just out of curiosity Wouldn't your open event process fire on all new
workbooks frustrating the guy when he is using Excel for something else? I
think I'd put something in there to check the type of the file being opened
it .txt

 

[Joel]

I don't like using personal.xls files because there are too many situations
that may occur when you open a workbook that fixing one problem may break
others. The only solution to your problem is to format the worksheet in text
before reading the data. You are starting with a text file which means your
worksheet will be initialized in the general format. Reading numbers into
the general format is causing your problem where general format is converting
text to numbers. To solve your problem you must first format the worksheet
into text before reading the data.