PS,

Change the line

iRow = rngEnd.Row - 1

to

iRow = rngEnd.Row

I forgot to add that the macro handles files with more than 65536 lines by
listing the extra data in as many adjacent columns as are needed.

Shockley




"shockley" wrote in message
...
Larry,

Here's a way to do it using FileSystemObject. I am calling the source file
"TestSource.txt" and I have 4 extra text files in the same folder called
"test01.txt", "test02.txt", "test03.txt", and "test04.txt". Make as many
of
these as you need to split the original source file into separate files of
max 65536 lines. There may be some minor bugs and some special cases where
it doesn't work exactly right, but the general idea is there and you
should
be able to modify it to your needs. You may have to add a reference for
Microsoft Scripting Runtime. You can get help on the FileSystemObject and
related properties and methods he

http://msdn.microsoft.com/library/de...us/vbenlr98/ht
ml/vaobjfilesystemobject.asp

HTH,
Shockley


Public Const SaveDir As String = "C:\Documents and
Settings\shockley\Desktop\"
Sub Macro1()
Set fso = CreateObject("Scripting.FileSystemObject")
Set oSourceFile = fso.GetFile(SaveDir & "testSource.txt")
Set tsRead = oSourceFile.OpenAsTextStream(ForReading,
TristateUseDefault)

Do
x = x + 1
Set oWriteFile = fso.GetFile(SaveDir & "test0" & x & ".txt")
Set tsWrite = oWriteFile.OpenAsTextStream(ForWriting,
TristateUseDefault)
LineCount = 0

Do
sTest = tsRead.ReadLine
tsWrite.WriteLine sTest
LineCount = LineCount + 1
If tsRead.AtEndOfStream Then Exit Do
Loop Until LineCount = 65536

Workbooks.OpenText _
FileName:=SaveDir & "test0" & x & ".txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))

Set wbk = ActiveWorkbook
With wbk.Sheets(1)
Set rngEnd = .Cells(1, 1).End(xlDown)
If Not rngEnd Is Nothing Then
iRow = rngEnd.Row - 1
Set rngSource = Range(.Cells(1, 1), .Cells(iRow, 1))
With ThisWorkbook.Sheets(1)
Set rngDest = Range(.Cells(1, x), .Cells(iRow, x))
End With
rngDest.Value = rngSource.Value
End If
End With
wbk.Close SaveChanges:=False
Loop Until LineCount < 65536
rngDest.Cells(iRow + 1) = " "
End Sub






"L Mehl" wrote in message
...
Another application creates text files which I import into a worksheet
for
further processing.

The text file looks like:
ARRAY 1 3023 <--3023 indicates how many X-values are in the file
#X-values, ...some more misc text ...
0.378228
0.737527
1.113739
1.488899
1.866231
2.257432
... more X-values
<--one space in this line
#Y-values
0.195559
0.152420
0.126161
0.199365
0.173638

... more Y-values

I need only the first 2 rows plus all the X-values (up to but not
including
the line containing one space.

There could be 65,500+ X-values, and line-by-line processing is slow
with
many values to import.

Is there a way in VBA to import a specified number of lines, without
using
a
line-by-line method?

expression.OpenText(...) has a StartRow parameter, but no "EndRow" as
far
as
I can tell.

Does anyone know of a text-file parsing program or a method which could
be
used to write a program which could be called by VBA, to create a new
file
after stripping the rows below and including the line containing one
space?

Thanks for any help.

Larry Mehl




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 2/28/2004