Re: Importing a text file into Excel with too many rows?

Hi Nexan,

You could, perhaps use the following MS macro:

Sub LargeFileImport()
'--------===============================
' See: MSKB # 120596
http://support.microsoft.com/default.aspx?scid=kb;en-us;120596
'=====================================
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g.
test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Work*** In It
Workbooks.Add template:=xlWork***
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65536 Then
'If On The Last Row Then Add A New ***
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False

End Sub

If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman



"Nexan" <Nexan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5386E204-57F2-4989-A3BA-6C7D6C7A90A5@xxxxxxxxxxxxxxxx
> My boss needs me to import a text file into Excel that contains too many
> rows
> for one work*** to handle. All of the tips I've been able to find
> regarding
> using Data Import Wizard have to do with dealing with too many columns,
> not
> too many rows. How can I fix this problem?
>
> Thanks!


.