Importing Text Files

A

Adam

Anyone know how to create an overflow for text files? I
import massive amounts of data from a text file into
Excel, and I always must do this several times after
opening text file and deleting top 62000 lines. I found
a macro online once, and had it set up on my computer,
but have since reformatted and lost the macro. Anyone
know where to find this macro again? Or know how to force
the import to continue on a second sheet?
THank you in advance for any help.....
Adam
 
J

jeff

HI,

Play with this code - it'll ask you how many lines to
put on a page before adding a new page and filling
it up, etc.

Private Sub CommandButton1_Click()
Dim fnam As String
Dim txtline As String
Dim j, pg As Long
txtline = InputBox("how many rows per sheet?", , 60000)
k = Val(txtline)
fnam = "C:\mydirectory\ImportMe.txt"
Open fnam For Input As #1
While Not EOF(1)
j = j + 1
If j > k Then
j = 1
Workbooks(1).Worksheets.Add
End If
Line Input #1, txtline
ActiveSheet.Range("A" & j) = txtline

Wend
Close #1
pg = Workbooks(1).Worksheets.Count
MsgBox ("All finished - " & pg & " sheets")
End Sub

good luck
jeff
 
Top