import tekst >65536 rows

[Reniek]

Hi,

If I have a txt file with data more than 65536 rows, is it possible to
import into Excell all the data divided into 2 sheets or the part will be
lost?

Thanks for any advice
IW

 

[Ashley Bragg]

Hi Reniek,

This is possible. Try the following code:

Sub test()

Dim currline As String
Dim counter As Long


counter = 1

Open "c:\Transaction.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, currline

If counter > 65536 Then
Sheets("sheet2").Select
End If

Cells(counter, 1) = currline
counter = counter + 1
Loop




End Sub

Replace "C:\Transation.txt" with your file. As you run out of rows on the
current sheet, it will spill over into sheet2. You can repeat for as many
sheets as you like.

Regards

Ashley
http://vbanut.blogspot.com

 

[Jim Cone]

If you have less than 98303 rows, then the following knowledge base article may help...

http://support.microsoft.com/?kbid=120596
See the section:
" Importing Text Files with Fewer than 98,303 Rows"

Jim Cone
San Francisco, USA

 

[Ashley Bragg]

Reniek

Noticed an error in my code. You also need to reset the counter once you
move onto the next sheet:

 

[Tom Ogilvy]

Think you have your references mixed up - although that is a good article:

http://support.microsoft.com/?kbid=120596
Importing text files larger than 65536 rows

Think you were citing an article using start line. Perhaps this one:

http://support.microsoft.com/default.aspx?scid=kb;en-us;119770
Text Import Wizard Maximum Start Row Limitation of 32767

which does have a section entitled:
"Excel 97 -- Importing Text Files with Fewer than 98,303 Rows"

 

[Jim Cone]

Tom,

Well I started out to reference 119770, but it somehow didn't end up that way. <g>

Thanks for being more alert than I am today.

Regards,
Jim Cone