I'm not sure I totally understand. When bring in a fixed length file I
get something like this:

Workbooks.OpenText FileName:= _
"C:\Documents and Settings\ca5ms\Desktop\name line.txt",
Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
1), Array(2, _
1), Array(11, 1), Array(24, 1), Array(31, 1), Array(36, 1),
Array(51, 1), Array(66, 1), _
Array(91, 1), Array(131, 1), Array(171, 1), Array(211, 1),
Array(241, 1), Array(246, 1), _
Array(257, 1), Array(259, 1), Array(264, 1), Array(274, 1),
Array(275, 1), Array(278, 1), _
Array(318, 1), Array(328, 1), Array(338, 1), Array(341, 1),
Array(346, 1), Array(356, 1), _
Array(357, 1), Array(363, 1), Array(372, 1), Array(382, 1)),
TrailingMinusNumbers:= _
True

Because i need to format the lines that start with say 02 differently
than those that start with 03. In your model where would I put the
line formatting?

Thanks!
Mary

Bernie Deitrick wrote:
Mary,

The macro below shows one way of doing this....

HTH,
Bernie
MS Excel MVP

Sub VariedLineImport()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double

FileName = Application.GetOpenFilename

If FileName = "" Then End

FileNum = FreeFile()
Open FileName For Input As #FileNum

Application.ScreenUpdating = False

Workbooks.Add Template:=xlWorksheet

Counter = 1

Do While Seek(FileNum) <= LOF(FileNum)

'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 2) = "01" Then
MsgBox "That line started with 01"
'do the splitting here, like
Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1)
'Etc
ElseIf Left(ResultStr, 2) = "02" Then
MsgBox "That line started with 02"
'do the splitting here, like
Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1)
'Etc
ElseIf Left(ResultStr, 2) = "03" Then
MsgBox "That line started with 03"
'do the splitting here, like
Cells(Counter, 1).Value = "'" & Left(ResultStr, InStr(1, ResultStr, " ") - 1)
'Etc
'And so on for different starting values
End If
'Increment the Counter By 1
Counter = Counter + 1
Loop
'Close The Open Text File
Close
End Sub



wrote in message
ups.com...
have a text file I need to convert to an excel spreadsheet. I
wouldn't have a problem making an array code if it were a straight file
but the trick is that each line could be different.

For example:

Line one is

01330948951000312 eeid jan boerenkamp

line 2 is

023330948951000312 00370058274800350010112/01/2002

Line 3 is

05330948951000312 31065327123107533811

any time a line begins with 01 it will follow that format

any time a line begins with 02 it will follow the format of line 2 and
so on

Is there a way to make an array to put into Excel so that if a line
starts with 01 it splits it one way and if a line begins with 02 it
splits another way and so on?

Thanks in advance