VBA Runtime error 14: Out of string space... help!

[smurray444]

Dear all,

I'm using the VBA code found at the following URL to import a large text
file into Excel 2007 across several worksheets:

http://support.microsoft.com/kb/120596/EN-US/

The only change that I've made is to update the maximum row number from
65536 to 1048576 to account for this version of Excel.

However, when I run the code I receive the following error:

Run-time error 14: Out of string space.

Does anyone have any ideas as to what this refers to and how I might go
about solving it? I've tried Googling this and it would suggest that maybe
memory is the limiting factor - however, I'm using a PC with 4GB of memory
(the text file I'm using is around 84MB)...!

Thanks for your help,
Steve

 

[CoRrRan]

Dear all,

I'm using the VBA code found at the following URL to import a large text
file into Excel 2007 across several worksheets:

http://support.microsoft.com/kb/120596/EN-US/

The only change that I've made is to update the maximum row number from
65536 to 1048576 to account for this version of Excel.

However, when I run the code I receive the following error:

Run-time error 14: Out of string space.

Does anyone have any ideas as to what this refers to and how I might go
about solving it? I've tried Googling this and it would suggest that maybe
memory is the limiting factor - however, I'm using a PC with 4GB of memory
(the text file I'm using is around 84MB)...!

Thanks for your help,
Steve

Perhaps you are encountering the limitation of the String datatype,
which is set at 2^31 characters in VBA. (At least, that's what the
limitation is for Excel 2003. I'm not sure whether Excel 2007 uses a
different version of VBA.)

Can you tell us on what line you get that error?

CoRrRan

 

[smurray444]

Hi - thanks for your reply.

When I select debug, the following is highlighted in yellow:
Line Input #FileNum, ResultStr

This VBA code I got from the Microsoft website (see original post), so
should in theory work. However, I'm open to suggestions for alternatives if
getting it to work appears unfeasible and someone is feeling creative!
(Basically the mission is to import a large text file of ~84MB into Excel,
splitting it over worksheets when the maximum number of rows has been reached.

Many thanks again,
Steve

 

[steve_doc]

Purely out of intrest

Quote
"The only change that I've made is to update the maximum row number from
65536 to 1048576 to account for this version of Excel"

were you getting the same error before you made this change?

Gut instinct tells me is a limitation error regarding the string variable,
so just ruling out the above option

 

[par_60056]

Purely out of intrest

Quote
"The only change that I've made is to update the maximum row number from
65536 to 1048576 to account for this version of Excel"

were you getting the same error before you made this change?

Gut instinct tells me is a limitation error regarding the string variable,
so just ruling out the above option










- Show quoted text -

Is it possible your file has no carriage returns and only has line
feeds or that there is at least 1 line that is HUGE?

Poking to see if it points to any ideas...

Peter Richardson

 

[smurray444]

Thanks for your replies again.

I have tried changing the maximum row value to a range of numbers (as low as
20!), but the same result occurs.

With regard to the latest point, the text file is fomatted as follows:

-67.45832 82.87501 0.2068079
-67.37498 82.87501 0.2068079
-67.29166 82.87501 0.2068079
-67.20832 82.87501 0.2068079
-67.12498 82.87501 0.2068079
-67.04166 82.87501 0.2068079
-66.95832 82.87501 0.2068079
-66.87498 82.87501 0.2068079
-66.79166 82.87501 0.2068079 ...etc etc...

However, when displayed in Notepad, the rectangle (carriage return?) symbol
separates each row (i.e. it is displayed as one long string and not in
columns as shown above... although the above is the result of a direct copy
and paste!). So maybe this is causing a problem? If so, is there a way of
getting round this? It would take ages to go though the text file
reformatting it and removing the symbols for example.


Any ideas would be very much appreciated.

Many thanks
Steve

 

[par_60056]

Thanks for your replies again.

I have tried changing the maximum row value to a range of numbers (as low as
20!), but the same result occurs.

With regard to the latest point, the text file is fomatted as follows:

-67.45832 82.87501 0.2068079
-67.37498 82.87501 0.2068079
-67.29166 82.87501 0.2068079
-67.20832 82.87501 0.2068079
-67.12498 82.87501 0.2068079
-67.04166 82.87501 0.2068079
-66.95832 82.87501 0.2068079
-66.87498 82.87501 0.2068079
-66.79166 82.87501 0.2068079 ...etc etc...

However, when displayed in Notepad, the rectangle (carriage return?) symbol
separates each row (i.e. it is displayed as one long string and not in
columns as shown above... although the above is the result of a direct copy
and paste!). So maybe this is causing a problem? If so, is there a way of
getting round this? It would take ages to go though the text file
reformatting it and removing the symbols for example.

Any ideas would be very much appreciated.

The file has line feeds without carriage returns so to the line input
function it is 1 LONG string.

You can try loading it with WORDPAD which should read it fine and look
right and then try saving it and see if it looks right in NOTEPAD. I
know that is a LONG processed with a file that huge.

That is normally from a file produced on a problem of a file that came
from a UNIX system. There are utilities on the new to convert UNIX to
DOS files. This simply adds the CR after the LF.

Peter Richardson

 

[par_60056]

Thanks for your replies again.

I have tried changing the maximum row value to a range of numbers (as low as
20!), but the same result occurs.

With regard to the latest point, the text file is fomatted as follows:

-67.45832 82.87501 0.2068079
-67.37498 82.87501 0.2068079
-67.29166 82.87501 0.2068079
-67.20832 82.87501 0.2068079
-67.12498 82.87501 0.2068079
-67.04166 82.87501 0.2068079
-66.95832 82.87501 0.2068079
-66.87498 82.87501 0.2068079
-66.79166 82.87501 0.2068079 ...etc etc...

However, when displayed in Notepad, the rectangle (carriage return?) symbol
separates each row (i.e. it is displayed as one long string and not in
columns as shown above... although the above is the result of a direct copy
and paste!). So maybe this is causing a problem? If so, is there a way of
getting round this? It would take ages to go though the text file
reformatting it and removing the symbols for example.

Any ideas would be very much appreciated.

Many thanks
Steve

Under the heading of proofread before hitting send that should read:

That is normally a problem of a file that came from a UNIX system.
There are utilities on the net to convert UNIX to DOS files. This
simply adds the CR after the LF.

Peter Richardson

 

[CoRrRan]

Thanks for your replies again.

I have tried changing the maximum row value to a range of numbers (as low as
20!), but the same result occurs.

With regard to the latest point, the text file is fomatted as follows:

-67.45832 82.87501 0.2068079
-67.37498 82.87501 0.2068079
-67.29166 82.87501 0.2068079
-67.20832 82.87501 0.2068079
-67.12498 82.87501 0.2068079
-67.04166 82.87501 0.2068079
-66.95832 82.87501 0.2068079
-66.87498 82.87501 0.2068079
-66.79166 82.87501 0.2068079 ...etc etc...

However, when displayed in Notepad, the rectangle (carriage return?) symbol
separates each row (i.e. it is displayed as one long string and not in
columns as shown above... although the above is the result of a direct copy
and paste!). So maybe this is causing a problem? If so, is there a way of
getting round this? It would take ages to go though the text file
reformatting it and removing the symbols for example.


Any ideas would be very much appreciated.

Many thanks
Steve

If this is the case, I would suggest you try a different approach:

****************************
Option Explicit

Sub ParseInput()

Dim FSO As Scripting.FileSystemObject
Dim TS As Scripting.TextStream

Dim i As Integer, j As Long

Dim sData As String, sArr() As String

Set FSO = New Scripting.FileSystemObject
Set TS = FSO.OpenTextFile("C:\Temp\Input.txt")

Range("A1").Activate

Do
sData = TS.ReadLine

If sData <> "" Then
sArr = Split(sData, " ")

For i = 0 To UBound(sArr)
If sArr(i) <> "" Then ActiveCell.Offset(j, i).Value = sArr(i)
Next i

j = j + 1

If j > 1048576 Then
Worksheets.Add After:=Worksheets.Count
Worksheets(Worksheets.Count).Activate
Range("A1").Activate
j = 0
End If

End If
Loop Until TS.AtEndOfStream = True

Set TS = Nothing
Set FSO = Nothing

End Sub
****************************

Make a reference to "Microsoft Scripting Runtime" (VBE --> Tools -->
References --> select "Microsoft Scripting Runtime" and close with "OK").

I don't think this piece of code will help you all the way, but it'll
give you a starting point. I also haven't tested it properly,

Regards,

CoRrRan