Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
smurray444 wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
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 "CoRrRan" wrote: smurray444 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
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 "smurray444" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
On Jul 24, 10:38 am, steve_doc
wrote: 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 "smurray444" wrote: 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- Hide quoted text - - 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
On Jul 24, 11:34 am, smurray444
wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Runtime error 14: Out of string space... help!
On Jul 24, 11:34 am, smurray444
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime 28 - Out of stack space | Excel Programming | |||
Number of space in a string | Excel Discussion (Misc queries) | |||
Can I find a string with or without a space after the last charact | Excel Programming | |||
how to remove a space after a string? | Excel Programming | |||
NEED HELP-----Removing a space at the end of a string of character | Excel Discussion (Misc queries) |