Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
import flat file
i have recorded the following macro:
Workbooks.OpenText Filename:="C:\Users\jat\Desktop\23-32237", Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1)), TrailingMinusNumbers:=True it opens the file 23-32237, and i have it inserting the width of each column, but i have to manually delete some also. example Array(39, 9) deletes the "preset" that windows recognizes based on some criteria, and Array (48, 1) adds one based on what i want. how do you delete all presets that windows puts in automatically? thank you, jat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
import flat file
What do you mean by preset?
Can you paste the value which is in your text file and what you get (and want) in Excel? "jatman" wrote: i have recorded the following macro: Workbooks.OpenText Filename:="C:\Users\jat\Desktop\23-32237", Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1)), TrailingMinusNumbers:=True it opens the file 23-32237, and i have it inserting the width of each column, but i have to manually delete some also. example Array(39, 9) deletes the "preset" that windows recognizes based on some criteria, and Array (48, 1) adds one based on what i want. how do you delete all presets that windows puts in automatically? thank you, jat |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
import flat file
the presets are all of the arrays that end as ",1". when i open the file in
Excel, i get the Text Import Wizard. in Step 1, select Fixed width. in Setp 2, i set field widths (column breaks, or the preset width that Windows detects). it is in this step of the macro that creates problems. some files have different column breaks that come up automatically. as for pasting the values from the text file, it shows up as a continous single line in the text file. jat "Sheeloo" wrote: What do you mean by preset? Can you paste the value which is in your text file and what you get (and want) in Excel? "jatman" wrote: i have recorded the following macro: Workbooks.OpenText Filename:="C:\Users\jat\Desktop\23-32237", Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1)), TrailingMinusNumbers:=True it opens the file 23-32237, and i have it inserting the width of each column, but i have to manually delete some also. example Array(39, 9) deletes the "preset" that windows recognizes based on some criteria, and Array (48, 1) adds one based on what i want. how do you delete all presets that windows puts in automatically? thank you, jat |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
import flat file
Just in case you don't know this...
FieldInfo:= Array( Array(0,1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1) ) tells [For FIXED WIDTH option] the macro to put the value in Cols 1-14, 15-39, 49-54... in separate columns in Excel with General formatting. and skip the value in Cols 40-48 (9 means skip column) So if you want to change what goes in which column from the text file you need to adjust the first number in each array and have the second number as 1 if you want General formatting and 9 if you want to skip... Also If there is a recognizible date in the data, the cell will be formatted as a date in the worksheet even if the setting for the column is General. Additionally, if you specify one of the date formats (3 to 8 as the second number, btw 2 means text) for a column and the data does not contain a recognized date, then the cell format in the worksheet will be General. Hope this helps... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "jatman" wrote: the presets are all of the arrays that end as ",1". when i open the file in Excel, i get the Text Import Wizard. in Step 1, select Fixed width. in Setp 2, i set field widths (column breaks, or the preset width that Windows detects). it is in this step of the macro that creates problems. some files have different column breaks that come up automatically. as for pasting the values from the text file, it shows up as a continous single line in the text file. jat "Sheeloo" wrote: What do you mean by preset? Can you paste the value which is in your text file and what you get (and want) in Excel? "jatman" wrote: i have recorded the following macro: Workbooks.OpenText Filename:="C:\Users\jat\Desktop\23-32237", Origin:= xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1)), TrailingMinusNumbers:=True it opens the file 23-32237, and i have it inserting the width of each column, but i have to manually delete some also. example Array(39, 9) deletes the "preset" that windows recognizes based on some criteria, and Array (48, 1) adds one based on what i want. how do you delete all presets that windows puts in automatically? thank you, jat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Flat file security | Excel Discussion (Misc queries) | |||
how do I convert an excel file to a flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
Flat File | Excel Discussion (Misc queries) |