import tekst >65536 rows

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

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

"Reniek" wrote:

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

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



"Reniek" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

Reniek

Noticed an error in my code. You also need to reset the counter once you
move onto the next sheet:
> 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
<<<<<<< counter=1
> End If
>
> Cells(counter, 1) = currline
> counter = counter + 1
> Loop
>
>
>
>
> End Sub


"Ashley Bragg" wrote:

> 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
>
> "Reniek" wrote:
>
> > 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
> >
> >
> >

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...b;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"


--
Regards,
Tom Ogilvy



"Jim Cone" <[email protected]> wrote in message
news:eeJ8%[email protected]...
> 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
>
>
>
> "Reniek" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >

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


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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...b;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"

> Regards,
> Tom Ogilvy