The EXCEL Help says that when importing a file exceeding exceeding 65536 you
can import the rest in another worksheet by setting a new range. However, in
doing so, it doestn't let you start at row 65537, the max it allows for the
next starting range is the old 32767 limit. When using that it doesn't
import 65k, it only import another 32K, so you get essentially repetitive
data on the second worksheet. Is there some way to get around this? My
users don't want to use a file splitter or Access or any other database.
They like Excel.

Excel never had a row limit of 32767

Excel jumped from 16384 to 65536 with the introduction of Excel 97.

The macro on this MS page imports 65536 rows to a sheet.

http://support.microsoft.com/default...;en-us;Q120596


Gord Dibben MS Excel MVP

On Tue, 11 Jul 2006 15:00:01 -0700, Mainframer at Heart <Mainframer at
wrote:

The EXCEL Help says that when importing a file exceeding exceeding 65536 you
can import the rest in another worksheet by setting a new range. However, in
doing so, it doestn't let you start at row 65537, the max it allows for the
next starting range is the old 32767 limit. When using that it doesn't
import 65k, it only import another 32K, so you get essentially repetitive
data on the second worksheet. Is there some way to get around this? My
users don't want to use a file splitter or Access or any other database.
They like Excel.

Thank you this was really helpful. I'll have to modify this to parse the data
into the cells, because my users aren't going to be able to deal with the
'Text To Columns' splitting for all the files we send them.

"Gord Dibben" wrote:

Excel never had a row limit of 32767

Excel jumped from 16384 to 65536 with the introduction of Excel 97.

The macro on this MS page imports 65536 rows to a sheet.

http://support.microsoft.com/default...;en-us;Q120596


Gord Dibben MS Excel MVP

On Tue, 11 Jul 2006 15:00:01 -0700, Mainframer at Heart <Mainframer at
wrote:

The EXCEL Help says that when importing a file exceeding exceeding 65536 you
can import the rest in another worksheet by setting a new range. However, in
doing so, it doestn't let you start at row 65537, the max it allows for the
next starting range is the old 32767 limit. When using that it doesn't
import 65k, it only import another 32K, so you get essentially repetitive
data on the second worksheet. Is there some way to get around this? My
users don't want to use a file splitter or Access or any other database.
They like Excel.

It helped, yes an not!

I cannot make it work.

I do understand Microsoft washes their hands and do not want to take
resposibility, but at least explain clearly what to do!!

I only understood to place thename of the file I wish to open within:
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")

then what?

I run it and I presumed I hadto input the folder path within the Macro
windows that appears after it was launched and then I get a debug problem on
line:
Open FileName For Input As #FileNum

Please give usa clear step by step explanation.

Thank you for your time.

"Mainframer at Heart" wrote:

Thank you this was really helpful. I'll have to modify this to parse the data
into the cells, because my users aren't going to be able to deal with the
'Text To Columns' splitting for all the files we send them.

"Gord Dibben" wrote:

Excel never had a row limit of 32767

Excel jumped from 16384 to 65536 with the introduction of Excel 97.

The macro on this MS page imports 65536 rows to a sheet.

http://support.microsoft.com/default...;en-us;Q120596


Gord Dibben MS Excel MVP

On Tue, 11 Jul 2006 15:00:01 -0700, Mainframer at Heart <Mainframer at
wrote:

The EXCEL Help says that when importing a file exceeding exceeding 65536 you
can import the rest in another worksheet by setting a new range. However, in
doing so, it doestn't let you start at row 65537, the max it allows for the
next starting range is the old 32767 limit. When using that it doesn't
import 65k, it only import another 32K, so you get essentially repetitive
data on the second worksheet. Is there some way to get around this? My
users don't want to use a file splitter or Access or any other database.
They like Excel.

bubu

The only caveat I see about folder name is if you are opening the *.txt file
from your desktop.

I tested with a text file with 128,609 records which the macro broke into two
sheets with 65536 and 63073 records.

I tested twice.

1. With the text file in C:\

2. With the text file in my default Excel folder.

When the inputbox comes up just enter the file name.....no drive or path.


Gord Dibben MS Excel MVP

On Sat, 27 Jan 2007 12:47:01 -0800, bubu wrote:

It helped, yes an not!

I cannot make it work.

I do understand Microsoft washes their hands and do not want to take
resposibility, but at least explain clearly what to do!!

I only understood to place thename of the file I wish to open within:
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")

then what?

I run it and I presumed I hadto input the folder path within the Macro
windows that appears after it was launched and then I get a debug problem on
line:
Open FileName For Input As #FileNum

Please give usa clear step by step explanation.

Thank you for your time.

"Mainframer at Heart" wrote:

Thank you this was really helpful. I'll have to modify this to parse the data
into the cells, because my users aren't going to be able to deal with the
'Text To Columns' splitting for all the files we send them.

"Gord Dibben" wrote:

Excel never had a row limit of 32767

Excel jumped from 16384 to 65536 with the introduction of Excel 97.

The macro on this MS page imports 65536 rows to a sheet.

http://support.microsoft.com/default...;en-us;Q120596


Gord Dibben MS Excel MVP

On Tue, 11 Jul 2006 15:00:01 -0700, Mainframer at Heart <Mainframer at
wrote:

The EXCEL Help says that when importing a file exceeding exceeding 65536 you
can import the rest in another worksheet by setting a new range. However, in
doing so, it doestn't let you start at row 65537, the max it allows for the
next starting range is the old 32767 limit. When using that it doesn't
import 65k, it only import another 32K, so you get essentially repetitive
data on the second worksheet. Is there some way to get around this? My
users don't want to use a file splitter or Access or any other database.
They like Excel.

I don't see the beginning of the thread.

I expect you were give a webpage to split data
between sheets and that it used Integer which
was sufficient for Excel 95.

If you are using a macro you would dimension a
variables dealing with rows (and near future with columns)
as Long and not as Integer as in the Microsoft examples.

If you are trying to cycle through a specified
number of rows as 65536 then you are writing
your macro inefficiently. And might want to look at
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/slowresp.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
bubu

The only caveat I see about folder name is if you are opening the *.txt file
from your desktop.

I tested with a text file with 128,609 records which the macro broke into two
sheets with 65536 and 63073 records.

I tested twice.

1. With the text file in C:\

2. With the text file in my default Excel folder.

When the inputbox comes up just enter the file name.....no drive or path.


Gord Dibben MS Excel MVP

On Sat, 27 Jan 2007 12:47:01 -0800, bubu wrote:

It helped, yes an not!

I cannot make it work.

I do understand Microsoft washes their hands and do not want to take
resposibility, but at least explain clearly what to do!!

I only understood to place thename of the file I wish to open within:
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")

then what?

I run it and I presumed I hadto input the folder path within the Macro
windows that appears after it was launched and then I get a debug problem on
line:
Open FileName For Input As #FileNum

Please give usa clear step by step explanation.

Thank you for your time.

"Mainframer at Heart" wrote:

Thank you this was really helpful. I'll have to modify this to parse the data
into the cells, because my users aren't going to be able to deal with the
'Text To Columns' splitting for all the files we send them.

"Gord Dibben" wrote:

Excel never had a row limit of 32767

Excel jumped from 16384 to 65536 with the introduction of Excel 97.

The macro on this MS page imports 65536 rows to a sheet.

http://support.microsoft.com/default...;en-us;Q120596


Gord Dibben MS Excel MVP

On Tue, 11 Jul 2006 15:00:01 -0700, Mainframer at Heart <Mainframer at
wrote:

The EXCEL Help says that when importing a file exceeding exceeding 65536 you
can import the rest in another worksheet by setting a new range. However, in
doing so, it doestn't let you start at row 65537, the max it allows for the
next starting range is the old 32767 limit. When using that it doesn't
import 65k, it only import another 32K, so you get essentially repetitive
data on the second worksheet. Is there some way to get around this? My
users don't want to use a file splitter or Access or any other database.
They like Excel.

This is a rather old thread more than 5 days old,
it was posted Tue, 11 Jul 2006,
the complete thread can be seen at
http://groups.google.com/groups?thre...0microsoft.com

And I notice Google Groups is up to more tricks in making
it hard to work with things more junk on the sides,
overlapping text (in Firefox) for those who don't work
full screen or use less that 1024 x 768 they are getting
hopeless. In fact the middle column with what you have to
view has to be scrolled horizontally.

The Microsoft article is in fact using Double instead
of Integer, so that is not a problem in being able to
address all of the rows. (Double is Floating Point)
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm