import flat file

[jatman]

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

 

[Sheeloo]

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]

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]

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...