Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a tab delimited text file
Thank you!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save CSV file as Pipe delimited text file | Excel Discussion (Misc queries) | |||
How can I save a file as a comma-delimited text file in Excel? | Excel Discussion (Misc queries) | |||
Recognize pipes "|" in when opening a delimited file in Excel | Excel Discussion (Misc queries) | |||
Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) | |||
Open delimited text file to excel without changing data in that file | Excel Programming |