opening a tab delimited text file
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 |
opening a tab delimited text file
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. |
opening a tab delimited text file
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. |
opening a tab delimited text file
Thank you!
|
All times are GMT +1. The time now is 04:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com