opening a tab delimited text file

[Keno]

Hi there,

I am using the workbooks.open method to open a tab
delimited text file. The problem is that I need the
format of one (or all) of the columns to be changed
to "text". The numbers in the column in question are
account numbers and are 16 digits long. They are
expressed in exponential form when I use the open method.
When I change the format of the column to "text" after
opening the file, it shows the exponential form as text.

If the above is not clear:
When opening a text file in excel manually, one is
normally taken through a wizard where you can choose the
delimiters etc. The last window in this wizard previews
the columns and allows one to change the format of any
column to text. Essentially, I need the VBA code that
will perform this operation.

Many thanks in advance!
keno

 

[Lonnie M.]

Hi, I adapted this from a previuos post. See the comments below to
select the data type for a given column.

' The Array(X,Y) in field info represents that column's data type
' 'X' represents the column
' 'Y' represents the data type
' 1 = General; 2 = Text; 3 = Date (MMDDYY)
Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:="~",
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2),
Array(5, 1), _
Array(6, 3), Array(7, 3))
Columns.AutoFit

HTH--Lonnie M.

 

[Lonnie M.]

Oops, I forgot to change this to Tab Delimeted, sorry about that...

Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2),
Array(5, 1), _
Array(6, 3), Array(7, 3))

Regards, Lonnie M.

 

[Guest]

Thank you!