Mary,

How, for example would you want

023330948951000312 00370058274800350010112/01/2002

split up? Are the last 10 characters a date? Any other meaningful splits, or just use spaces?

Post samples of how each string needs to be split, and we can help you with the code to do it.

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
I'm totally down with that. however, i'm just trying to figure out
where to update the code so that I can put in my markers so it
delineates in the places I need.

Tom Ogilvy wrote:
Just to be clear, the code suggested (and mine) would be in lieu of your
recorded code. It represents a different approach.

--
Regards,
Tom Ogilvy


"NickHK" wrote:

Excel will not be able to import it on own.
Whilst good at many tasks, it cannot guess your intenetions.
So you have to help out with some extra code.
See the other responses.

NickHK


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