I have a 100,000+ character linear text file that I would like convert
to a worksheet.
Each record will be 178 characters long. I feel certain that a visual
basic module would do the
job, but it is beyond my capabilities at this point. Thanks in advance
for any ideas.

Matthew Saxon

Hi
you may have a look at the following macro
http://support.microsoft.com/default...;EN-US;q120596

though written for Excel 95 the changes for Excel97+ are indicated

--
Regards
Frank Kabel
Frankfurt, Germany

Matthew wrote:
I have a 100,000+ character linear text file that I would like
convert
to a worksheet.
Each record will be 178 characters long. I feel certain that a
visual
basic module would do the
job, but it is beyond my capabilities at this point. Thanks in
advance for any ideas.

Matthew Saxon

have you tried just opening the file in excel.

when you say linear, do you mean the it is 100,000+ lines long (single
character on each line) and you want to build lines of 178 characters from
that?

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy

"Matthew" wrote in message
...
I have a 100,000+ character linear text file that I would like convert
to a worksheet.
Each record will be 178 characters long. I feel certain that a visual
basic module would do the
job, but it is beyond my capabilities at this point. Thanks in advance
for any ideas.

Matthew Saxon

No, sorry. The file is one line with 100,000 plus characters. It is a
file from a bank database.

"Tom Ogilvy" wrote in message
...
have you tried just opening the file in excel.

when you say linear, do you mean the it is 100,000+ lines long (single
character on each line) and you want to build lines of 178 characters
from
that?

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy

"Matthew" wrote in message
...
I have a 100,000+ character linear text file that I would like
convert
to a worksheet.
Each record will be 178 characters long. I feel certain that a
visual
basic module would do the
job, but it is beyond my capabilities at this point. Thanks in
advance
for any ideas.

Matthew Saxon

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long, i as long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
for i = 1 to len(LineofText)
sStr = sStr & Mid(lineofText,i,1)
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Next
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

If that doesn't work, we will need to read the line in in pieces - post back
if it doesn't work.

--
Regards,
Tom Ogilvy


"Matthew" wrote in message
...
No, sorry. The file is one line with 100,000 plus characters. It is a
file from a bank database.

"Tom Ogilvy" wrote in message
...
have you tried just opening the file in excel.

when you say linear, do you mean the it is 100,000+ lines long (single
character on each line) and you want to build lines of 178 characters
from
that?

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
rw = 0
Open "C:\FILEIO\TEXTFILE.TXT" For Input As #1
sStr = ""
Do While Not EOF(1)
Line Input #1, LineofText
sStr = sStr & lineofText
if len(sStr) = 178 then
rw = rw + 1
cells(rw,1).Value = sStr
sStr = ""
End if
Loop
'Close the file
if len(sStr) 0 then
cells(rw,1).Value = sStr
End if
Close #1
End Sub

--
Regards,
Tom Ogilvy

"Matthew" wrote in message
...
I have a 100,000+ character linear text file that I would like
convert
to a worksheet.
Each record will be 178 characters long. I feel certain that a
visual
basic module would do the
job, but it is beyond my capabilities at this point. Thanks in
advance
for any ideas.

Matthew Saxon