Hi,

I am trying to write a macro where I have to import a large text or CSV
file. The number of rows in the file is something like 805000 (number of
columns if just 2). Obviously excel cannot handle rows greater than
65536.
I want to open 1000 rows at a time and put it in the excel sheet, then
open another 1000 and so on. How can I do this ? Please help. If you
have a better way of doing this please let me know as well. Thanks

Srikanth



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

when i have to use a file larger than what excel can handle I usaull
just open it with access first so then import to excel 65000 cells a
a time, while its not the most wieldy it does work

Cheer

--
clan
-----------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...fo&userid=1186
View this thread: http://www.excelforum.com/showthread.php?threadid=27502

Sub LargeFileImport()

Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
FileName = InputBox("Please enter the Text File's name, e.g.
S:\Revenue\Straus\BATCHOCCMUM121002.txt")
If FileName = "" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False
Workbooks.Add template:=xlWorksheet
Counter = 1
Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
Line Input #FileNum, ResultStr
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If

If ActiveCell.row = 65536 Then
ActiveWorkbook.Sheets.Add
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop
Close
Application.StatusBar = False

End Sub


clane wrote in message ...
when i have to use a file larger than what excel can handle I usaully
just open it with access first so then import to excel 65000 cells at
a time, while its not the most wieldy it does work

Cheers