"Start import at row"-limit when importing text to worksheet



Hi there!

I'm using Excel 2003 to do analyse large textfiles, to do this I regularly
imports text to Excel, but unfortunately I've realized that there are some
limitations when importing text files.

I have a text file which has more that 65536 lines (which is the maximum row
numbers in one Excel worksheet), when I import this in Excel I first fill one
Now - the great thing is that I should be able to import the rest of the
textfile on another worksheet, so I start the "Text Impor Wizard", but when
using the feature "Start import at row" - the maximum row number I can type
is 32767... :-(

Of course I can start deleting lines from my textfile - but...

Please - Would you plan on changing this, so that this scenario would be
possible to solve?

Best Regards!

Dave Peterson

You may want to send your suggestion to MS. These newsgroups are usually just
people helping people. MS doesn't monitor every post (as far as I know...).

email your request to:
(e-mail address removed)


I'm not good at English, so I don't know whether this is what you expected or not.
But I think this could be done by using vba, if I've not misunderstood your
in my case(Excel 2002), i could import textfile with about 120000 lines by using
macros under though it took some time to import all data.

Sub importtext()
Dim fs, f, fss
Dim startrow As Long, endrow As Long, inrow As Long, count As Long
Dim retstring As String

'the row number where data is first entered(this could be changed if you
like )
startrow = 1

'the row number where data is last entered(this could be changed if you like)
endrow = Selection.Row

'the line number in a textfile where data start to be imported
'(this could be changed if you like)
startline = 1

Set fs = CreateObject("Scripting.FileSystemObject")

MsgBox "Select textfile to import"

Set fss = Application.FileDialog(msoFileDialogFilePicker)

If fss.Show = True Then

Exit Sub
End If

On Error Resume Next

Set f = fs.OpenTextFile(fss.SelectedItems.Item(1), 1, False, -2)

If IsEmpty(f) Then
MsgBox "Can't open " & fss.SelectedItems.Item(1)
Exit Sub
End If

On Error GoTo 0

inrow = startrow
count = 1

Do While f.AtEndOfStream <> True
retstring = f.ReadLine

If count >= startline Then
'the cell where data is entered
Cells(inrow, "a").Value = retstring
Application.StatusBar = "processing line number = " & count
inrow = inrow + 1
End If

count = count + 1

If inrow > endrow Then
Worksheets.Add after:=ActiveSheet
inrow = startrow
End If

End Sub


Great thanks!

Although I hopefully can manage to get this to work, lot's of people don't
know VBA, so I guess they would prefer the slight change in the GUI.


But thanks for the solution anyway!


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question