Text in excel.
Posted by James on January 31, 2000 2:31 PM
Is there a way to force excel to leave text values alone when opening a text file that was created in another program? As it stands it converts "0:0 1/4" to a date when it is just text representing a length. The files are tab delimited and have lots of columns in them. Some of with are values like the above that excel messes with if you just open it. You can go through the wizard and make all columns "text" in stead of "general" but is there a switch that makes that automatically the default column format?
Thanks for any help.
Posted by JAF on February 02, 2000 5:38 AM
This is a quick and dirty fix - there may be a more elegant way of doing this, but this is what I use at the moment . .
Sub Specify_Text()
Workbooks.OpenText FileName:="C:\My Documents\test.txt", Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2))
End Sub
The important bit of the code is the Array(x, y) where the second digit should always be 2. This instructs Excel to use the second option on the import - ie Text.
If you've got loads of columns, it will be a little time consuming, although it may be possible to add in some additional code to count the number of columns and incluse that as a variable (now there's a challenge for someone!) Even if you can't do this, as long as your source file always contains the same number of columns, you will only need to set up the acro once.
JAF.
