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

[Guest]

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
worksheet.
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)

 

[Guest]

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
writing.
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

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

'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

Else
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
Loop

End Sub

 

[Guest]

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!

-Mikkel