automatically open .txt file with all fields as text?
 

I need to be able to open a comma separated .txt file so that all fields
are automatically formated as text. I need this so that fields such as
"0003" are not converted to numbers which show up as "3". I need to have
it imported as "0003". Since there are over 150 fields per record, using
the import wizard and manually setting each column as text is less than
desirable. Any way to do this automatically?

If you record a macro when you open the workbook, you'll see something like:

FieldInfo:=Array(Array(1, 2), ....

That 2 means that field should be treated as text. You can create your own
array so you don't have to do all 256 (potential) fields.


Option Explicit
Sub testme01()

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long
Dim fName As Variant

fName = Application.GetOpenFilename(filefilter:="Text Files, *.txt")

If fName = False Then
Exit Sub 'user hit cancel
End If

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)
For iCtr = 1 To 256
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 2
Next iCtr

Workbooks.OpenText Filename:=fName, Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=myArray

End Sub

Jerry wrote:

I need to be able to open a comma separated .txt file so that all fields
are automatically formated as text. I need this so that fields such as
"0003" are not converted to numbers which show up as "3". I need to have
it imported as "0003". Since there are over 150 fields per record, using
the import wizard and manually setting each column as text is less than
desirable. Any way to do this automatically?

--

Dave Peterson

Thanks Dave. It works perfectly.

--
Jerry