I have a space delimmited text file. I have read up on how to import
this using VBA and come across the script at this site

http://www.cpearson.com/excel/imptext.htm

This worked great until I imported a text file with 32767 rows in. (I
doubt it is coincedence that this is exactly 50% of the top limit)

I don't think the results will ever be over the limit of 65536 so I do
not need to use any error checking for that.

Basically I get an error stating "Runtime Error 6 Overflow" and when I
stop the macro I notice all text has been imported up to row 32767

Can anyone help?

Thanks if you can

Dan

I think this is an issue with the Integer limit.

Integer variables are stored as 16-bit (2-byte) numbers ranging in value
from -32,768 to 32,767.

Chnage the code for:
Dim RowNdx As Integer to Dim RowNdx As Long



wrote in message
ups.com...
I have a space delimmited text file. I have read up on how to import
this using VBA and come across the script at this site

http://www.cpearson.com/excel/imptext.htm

This worked great until I imported a text file with 32767 rows in. (I
doubt it is coincedence that this is exactly 50% of the top limit)

I don't think the results will ever be over the limit of 65536 so I do
not need to use any error checking for that.

Basically I get an error stating "Runtime Error 6 Overflow" and when I
stop the macro I notice all text has been imported up to row 32767

Can anyone help?

Thanks if you can

Dan

Thanks a million!

That beastie worked a treat. I owe you a pint of cold lager :)

DKIM wrote:

I think this is an issue with the Integer limit.

Integer variables are stored as 16-bit (2-byte) numbers ranging in value
from -32,768 to 32,767.

Chnage the code for:
Dim RowNdx As Integer to Dim RowNdx As Long



wrote in message
ups.com...
I have a space delimmited text file. I have read up on how to import
this using VBA and come across the script at this site

http://www.cpearson.com/excel/imptext.htm

This worked great until I imported a text file with 32767 rows in. (I
doubt it is coincedence that this is exactly 50% of the top limit)

I don't think the results will ever be over the limit of 65536 so I do
not need to use any error checking for that.

Basically I get an error stating "Runtime Error 6 Overflow" and when I
stop the macro I notice all text has been imported up to row 32767

Can anyone help?

Thanks if you can

Dan

Yes, that was a mistake in my code to declare RowNdx as an Integer. As you
have seen, it needs to be declared As Long. The problem never got caught
during testing because I never imported a file with more that 32K rows.

If you're importing more the 64K rows, see
http://www.cpearson.com/excel/ImportBigFiles.htm for procedures to a import
unlimited number of rows.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

wrote in message
ups.com...
Thanks a million!

That beastie worked a treat. I owe you a pint of cold lager :)

DKIM wrote:

I think this is an issue with the Integer limit.

Integer variables are stored as 16-bit (2-byte) numbers ranging in value
from -32,768 to 32,767.

Chnage the code for:
Dim RowNdx As Integer to Dim RowNdx As Long



wrote in message
ups.com...
I have a space delimmited text file. I have read up on how to import
this using VBA and come across the script at this site

http://www.cpearson.com/excel/imptext.htm

This worked great until I imported a text file with 32767 rows in. (I
doubt it is coincedence that this is exactly 50% of the top limit)

I don't think the results will ever be over the limit of 65536 so I do
not need to use any error checking for that.

Basically I get an error stating "Runtime Error 6 Overflow" and when I
stop the macro I notice all text has been imported up to row 32767

Can anyone help?

Thanks if you can

Dan